SQL/XML Functions Working With Column Aliases, and Correlated Subqueries

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