[teiid-issues] [JBoss JIRA] (TEIID-4112) ORA-32039: recursive WITH clause must have column alias list
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)