Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

PK_UpNorthMay 2 2017 — edited Jun 14 2017

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

This post has been answered by odie_63 on May 2 2017
Jump to Answer

Comments

TexasApexDeveloper

Might want to post this to the proper forum.. This is the ORDS (Formerly APEX Listener) forum.. The APEX support forum is located here:Application Express

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

TexasApexDeveloper

Also here is an example of doing an Org tree in APEX using css3 coding: http://rokitta.blogspot.com/2013/12/pure-css3-org-tree-with-apex-list.html

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Edwin T

Thanks. The second solution worked.

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 12 2017
Added on May 2 2017
13 comments
2,564 views