[teiid-issues] [JBoss JIRA] (TEIID-4112) ORA-32039: recursive WITH clause must have column alias list

[Debbie Steigner (JIRA)]

Debbie Steigner created TEIID-4112:
--------------------------------------

             Summary: ORA-32039: recursive WITH clause must have column alias list
                 Key: TEIID-4112
                 URL: https://issues.jboss.org/browse/TEIID-4112
             Project: Teiid
          Issue Type: Bug
    Affects Versions: 8.7.2.6_2
            Reporter: Debbie Steigner
            Assignee: Steven Hawkins


If running a WITH table AS(...) query to Oracle and the query schema name is the same as the subquery name i.e. EWI.  you receive the Oracle error:

ORA-32039: recursive WITH clause must have column alias list

If you modify the query to 
WITH EWI1 AS ....
then it works.

Query:
======================================================
with EWI AS (
      SELECT
         A.cis_code,
         B.ewi_id,
         B.Action_Code,
         B.Action_Comments,
         B.Action_Date,
         VP.LONG_NAME AS "PERSON_LONG_NAME",
         B.Action_Taken_By_ECD_ID,
         D.Notes,
         D.Notes_Date,
         C.EWI_Sub_Type_Code,
         C.Narrative,
         C.Last_Update_Date,
         C.Last_Updated_By
      FROM
         "V_PARTY_STATUS" A inner join v_ewi_action B On A.cis_code = B.cis_code inner join v_ewi C ON B.ewi_id = C.ewi_id INNER JOIN V_PERSON VP ON B.Action_Taken_By_ECD_ID = VP.ECD_ID
         AND B.ACTION_TAKEN_BY_RACFID = VP.WINDOWS_LOGON
         AND B.ACTION_TAKEN_BY_DOMAIN = VP.DOMAIN left outer join v_ewi_notes D ON C.ewi_id = D.ewi_id
      where
         A.agreed_monitoring_code='ENHANCED_MONITORING'
         AND A.party_status_code not in ('INAC',
         'GARB')
         AND B.Action_Code = 'ENHANCED_MONITORING'
         AND B.task_status_code = 'FINISHED'
   ) select * from EWI
======================================================




--
This message was sent by Atlassian JIRA
(v6.4.11#64026)