All,
I'm developing on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit. I wasn't sure under which group to place this question as it touches on both a general SQL restriction and XML_DB functions.
I am translating legacy debit, credit, and invoice reports to XML. I'm using the Oracle XML_DB functions to construct a query for each that will output the respective reports in XML. The invoice query is long with a number of complex qualifications that need to be met. This, in turn, adds to the complexity of the XML query.
When I'm aggregating the invoice data across the order lines, I have required invoice information and/or conditions that are provided via subqueries. The subqueries are dependent on order line information passed down from a higher order line level query for use in the subquery where clause. I've been building and running the full query in stages.
My question pertains to the Oracle restriction on referencing aliases down to only one level in correlated subqueries. I'm hoping that someone can help me understand the difference in behavior I'm seeing between column aliases used in one subsection of the aggregate query vs. that seen in a different subsection of the aggregate query.
The first subsection (I have it delimited with comments below) incorporates what I would consider correlated subqueries, but runs as expected and does not throw any error messages associated with the aliased columns in the where clause. The second subsection (also delimited with comments) won't run as it throws the error, PL/SQL: ORA-00904: "<aliased column name>": invalid identifier error.
I recognize this is a rather unfamiliar and complex section of code and that I'm not providing data sets that can be used to actually run the query. I really looking for an idea or work around just to get it running again. I'm not clear as to why the 2 sections don't behave in a similar manner. Can someone here clarify this for me so I can come to an understand of what is causing the difference in behavior? Also, does someone know of a workaround for the Oracle one level limitation on aliases used in correlated subqueries?
Here is the relevant portion of the code:
xmlelement("ITEMS",
(SELECT
xmlagg(
xmlelement("ITEM",
xmlforest(
Q.C56ORDNO AS "OrderLine",
Q.C56SUB AS "Sub_Order",
Q.C56SNO AS "Shipment_No",
Q.C56OLINE AS "Order_Line",
Q.C45MLINE AS "Assembly_Line",
Q.C56SPR AS "Standard_Price",
Q.C56DPR AS "Discount_Price",
Q.C56QOO AS "Qty_On_Order",
Q.C56QSH AS "Qty_Shipped",
Q.C56QBO AS "Qty_On_BO",
Q.C56IPR AS "Rlz_Item_Prc",
Q.C56BVEPR AS "RlzInvc_ExtPrc",
(SELECT CASE
WHEN Q.C45LTYP = 'N' THEN Q.C45MLINE||' '||Q.C45PART
WHEN Q.C45MUNIT = 'MARK UNIT-' OR Q.C45MUNIT = 'MARK UNIT-0' THEN TO_CHAR(Q.C45MLINE)
ELSE Q.C45MLINE||' '||SUBSTR(Q.C45MUNIT,1,32)
END CASE FROM DUAL) AS "Line_Header",
Q.C45LTYP AS Line_Item_Typ,
Q.C45PART AS "Part_Number",
NVL(Q.C45MTYP,'N') AS "Master_Line_Itm",
DECODE(NVL(Q.C45MTYP,'N'),'N','S',Q.C45PTYP) AS "Part_Typ",
Q.C45IPR AS "Item_Prc",
Q.C45LABEL AS "Check_Point",
----* First section functions with column aliases used in correlated subqueries
(SELECT DISTINCT CASE
WHEN E.I40WONUM IS NOT NULL THEN E.I40WONUM --C1
ELSE
(SELECT DISTINCT CASE
WHEN F.I40WONUM IS NOT NULL THEN F.I40WONUM --C2 and I40C#1
ELSE
(SELECT DISTINCT CASE
WHEN G.I40WONUM IS NOT NULL THEN G.I40WONUM --I40C#2
ELSE
(SELECT DISTINCT CASE
WHEN J.I40WONUM IS NOT NULL THEN J.I40WONUM
ELSE NULL
END
FROM I40F J, C45F K, M96F, C40F L
WHERE K.C45ORDNO = L.C40ORDNO
AND K.C45OLINE = H.C45OLINE
AND TID = 'ORTP'
AND ARG = L.C40ORTP
AND M96AFCT6 = 'Y'
AND L.C40OORDNO = C40ORDNO
AND J.I40SOREF = L.C40OORDNO
AND J.I40SOLIN = C45OLINE
AND (C40ORDNO, C45OLINE) IN (SELECT DISTINCT C40ORDNO, C45OLINE
FROM C45F, M96F, C40F
WHERE C45ORDNO = C40ORDNO
AND C45OLINE = Q.C56OLINE
AND TID = 'ORTP'
AND ARG = I.C40ORTP
AND M96AFCT6 = 'Y'
AND C40OORDNO = C50TAB(j).C50ORDNO))
END
FROM I40F G, C45F H, M96F, C40F I
WHERE H.C45ORDNO = I.C40ORDNO
AND H.C45OLINE = Q.C56OLINE
AND TID = 'ORTP'
AND ARG = I.C40ORTP
AND M96AFCT6 = 'Y'
AND I.C40OORDNO = C50TAB(j).C50ORDNO
AND G.I40SOREF = I.C40OORDNO
AND G.I40SOLIN = Q.C56OLINE)
END
FROM I40F F
WHERE I40SOREF = Q.C56ORDNO
AND I40SOLIN = Q.C56OLINE
AND I40WOREF IS NULL)
END
FROM I40F E, F32F, F35F
WHERE E.I40WONUM = F32WONUM
AND E.I40WOREF IS NULL
AND F32UNO = F35ASSY
AND F35ORDNO = Q.C56ORDNO
AND F35OLINE = Q.C56OLINE
AND F32WONUM IS NOT NULL) AS WorkOrder,
----* End first section
----* Begin second section with correlated subqueries that don't work with column aliases
(SELECT xmlagg(xmlelement("Detail_Line",C47TEXT))
FROM (SELECT C47TEXT, C47NID,C47DSEQ
FROM C47F, C41F
WHERE C47NID = C41NID
AND C41ORDNO = C50TAB(j).C50ORDNO
AND C41OLINE = C56OLINE
AND ((C47TEXT IS NULL AND NVL(C47IPR,0)!=0) OR
(LTRIM(C47TEXT,' ')!= 'TOTAL LIST PRICE'))
AND C41NDST = 'B'
AND NVL(C41NSRC,'USER') IN ('MQS','USER')
AND ((Q.C45PTYP !='R') OR NVL(Q.C45LABEL,'X')!='PRA@08C')
AND (Q.C45MTYP = 'Y')
AND (Q.C45LTYP = 'P')
AND (C47SSLINE IS NULL OR (C47SSLINE IN
(SELECT C56OLINE
FROM C56F
WHERE C56ORDNO=C50TAB(j).C50ORDNO
AND C56SNO=C50TAB(j).C50SNO)))
UNION
SELECT C47TEXT,C47NID, C47DSEQ
FROM C47F, C41F
WHERE C47NID = C41NID
AND C41ORDNO = C50TAB(j).C50ORDNO
AND C41OLINE = Q.C56OLINE
AND C47TEXT IS NOT NULL
AND C41NDST = 'B'
AND NVL(C41NSRC,'USER') = 'O/E'
AND Q.C45PTYP ='N'
AND Q.C45MTYP = 'Y'
AND Q.C45LTYP = 'N'
UNION
SELECT (C45PART||' - '||I12DESC) C47TEXT, 0,0
FROM I12F,C45F
WHERE I12PART = Q.C45PART
AND Q.C45PTYP='R'
AND C45LABEL = 'PRA@08C'
AND Q.C45LTYP='P'
AND I12PART=C45PART
AND C45ORDNO=C50TAB(j).C50ORDNO
AND C45OLINE=Q.C45OLINE
AND C45MLINE=Q.C45MLINE
UNION
SELECT (C03PART||' - '||C03DESC) C47TEXT,0,5
FROM C03F
WHERE C03PART = Q.C45PART
AND Q.C45LTYP='N'
AND Q.C45PTYP='R'
UNION
SELECT (SELECT SUBSTR(I40DESC1,1,47)
FROM I40F
WHERE I40WONUM = WorkOrder
AND I40WOREF IS NULL) C47TEXT1,0,1
FROM DUAL
WHERE Q.C45PTYP !='R'
AND (Q.C45MTYP = 'N')
AND (Q.C45LTYP = 'P')
UNION
SELECT (SELECT SUBSTR(I40DESC2,1,47)
FROM I40F
WHERE I40WONUM = WorkOrder
AND I40WOREF IS NULL) C47TEXT2,0,2
FROM DUAL
WHERE Q.C45PTYP !='R'
AND (Q.C45MTYP = 'N')
AND (Q.C45LTYP = 'P')
AND C47TEXT2 IS NOT NULL
UNION
SELECT (SELECT SUBSTR(I40DESC3,1,47)
FROM I40F
WHERE I40WONUM = WorkOrder
AND I40WOREF IS NULL) C47TEXT3,0,3
FROM DUAL
WHERE Q.C45PTYP !='R'
AND (Q.C45MTYP = 'N')
AND (Q.C45LTYP = 'P')
AND C47TEXT3 IS NOT NULL
UNION
SELECT (SELECT SUBSTR(I40DESC4,1,47)
FROM I40F
WHERE I40WONUM = WorkOrder
AND I40WOREF IS NULL) C47TEXT4,0,4
FROM DUAL
WHERE Q.C45PTYP !='R'
AND (Q.C45MTYP = 'N')
AND (Q.C45LTYP = 'P')
AND C47TEXT4 IS NOT NULL
UNION
SELECT I12DESC,0,5
FROM I12F
WHERE I12PART = Q.C45PART
AND Q.C45LTYP='P'
AND Q.C45PTYP='S'
AND WorkOrder IS NULL
UNION
SELECT C03DESC,0,6
FROM C03F
WHERE C03PART = Q.C45PART
AND Q.C45LTYP='N'
AND Q.C45PTYP='S'
AND WorkOrder IS NULL
ORDER BY 2,3)) AS LINETEXT,
-----* End second section
xmlforest((SELECT
CASE
WHEN C40ORTP = '07' THEN 'FIELD SERVICE'
WHEN C40ORTP = '13' THEN 'DISCREP RPT'
ELSE NULL
END CASE
FROM C40F
WHERE C40ORDNO = C50TAB(j).C50ORDNO
AND C40AHL1 = C50TAB(j).AHL1
) AS "FS_Desc", --(SELECT CASE
(SELECT xmlforest(C25RFORD AS "Orig_Order",
C25RC AS "Record_Code",
C25VAN AS "Van",
CASE
WHEN C25OMON IS NOT NULL AND C25OYEAR IS NOT NULL
THEN C25OMON||'/'||C25ODAY||'/'||C25OYEAR
WHEN C25OYEAR IS NOT NULL
THEN
CASE
WHEN C25OYEAR BETWEEN 90 AND 99 THEN '19'||C25OYEAR
ELSE '20'||C25OYEAR
END
ELSE
NULL
END AS "Original_Date",
CASE
WHEN C25FSPT IS NOT NULL THEN (SELECT NVL(SUBSTR("FUNCTION",1,30),'UNKNOWN')
FROM M96F
WHERE TID = 'FSPT'
AND ARG = C25FSPT)
ELSE NULL
END AS "Problem_Type",
C25DRI AS "Driver_Initials",
C25LOI AS "Loader_Initials",
xmlcdata(C25CMNT) AS "Comments")
FROM C25F
WHERE C25ORDNO = C50TAB(j).C50ORDNO
AND C25MLINE = Q.C45MLINE
AND (SELECT NVL(M96AFCT3,'N')
FROM M96F
WHERE TID = 'ORTP'
AND ARG = C40ORTP) IN ('Y','y')
) AS "Quote_FS" --Select xmlforest C25RFORD
) AS "Field_Service"--xmlforest ((SELECT CASE
) --ITEM, xmlforest
) --xmlelement ITEM
) AS "FOO" FROM (SELECT X.C56ORDNO --xmlagg xmlelement ITEM
,X.C56SUB
,X.C56SNO
,X.C56OLINE
,A.C45MLINE
,X.C56SPR
,X.C56DPR
,X.C56QOO
,X.C56QSH
,X.C56QBO
,X.C56IPR
,X.C56BVEPR
,A.C45MUNIT
,A.C45LTYP
,A.C45PART
,A.C45MTYP
,A.C45PTYP
,A.C45IPR
,A.C45LABEL
FROM C45F A, C56F X
WHERE C56ORDNO = C50TAB(j).C50ORDNO
AND C56SNO = C50TAB(j).C50SNO
AND C45ORDNO = C56ORDNO
AND C45SUB = C56SUB
AND C45OLINE = C56OLINE
ORDER BY 4,5) Q
)
)
Thanks,
Paul