13 Replies Latest reply on Jun 14, 2017 5:51 PM by PK_UpNorth

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

    PK_UpNorth

      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

        • 1. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
          odie_63

          Hi Paul,

           

          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.

          Which identifier(s) does it complain about in your example?

           

          Without going into details right now, from my personal experience, I can advise the following :

           

          1- Minimize usage of XMLForest function.

          Although it seems handy when dealing with NULLs, I almost never use it, except for generating leaf nodes out of simple expressions (i.e. not entire subqueries).

           

          2- Modularize.

          Use functions to generate complex XML fragments, or pipelined table functions to produce data sets.

          1 person found this helpful
          • 2. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
            PK_UpNorth

            Odie_63,

                 Thanks for looking this over and your input.  The column name complained about in the example is Q.C45LTYP on example line 97.  However, I've tried running the query with that line commented out, and the complaint just moves to another Q aliased column.

             

                 In this case, the particular problematic subsection is using the xmlagg function rather than the xmlforest.  Do they have the same caveat in your view?

             

                 Unfortunately, at this point, if I move to modularize, I'll have to go back to the drawing board and redesign.  I'll also need to look into pipelined table functions, which I'm not familiar with, not a bad idea in the long run but...time. Ultimately, I may have to do that as this may be an impasse, but I hesitate.

             

                 As an aside, but part of the original question, am I correct that Oracle is handling the column aliases in the correlated subqueries of the 2 sections differently?

             

            Thanks again,

            Paul

            • 3. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
              Paulzip

              Obviously I'm not familiar with your tables or have test data to test my hypothesis, by I suspect your error relates to the fact that ANSI SQL table references (correlation names) are only scoped to just one level deep. 

               

              A simple demo...

               

              select (select count(*)

                      from  (select *

                             from  dual e

                             where  e.dummy = d.dummy)) res

              from  dual d

               

              ORA-00904: "D"."DUMMY": invalid identifier

               

              select (select count(*)

                      from  dual e

                      where  e.dummy = d.dummy) res

              from  dual d

               

              res

              1

               

               

              1 person found this helpful
              • 4. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                PK_UpNorth

                Paulzip,

                      Yes, the complexity of the table structure and the data preclude me from being able to provide table and data set examples that would be of any worth; I know that limits things. On the other hand,  I think that the error here implies that the issue is more one with the structure than the data.  I agree, the error seems to stem from the limitation on using aliases down to only one level deep in correlated subqueries. 

                    

                     However, aren't these queries, taken from the first section, 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)

                 

                If I run the query in its entirety, without the second section, it works fine.  The section shown above (designated the first section in the initial example), which, by my understanding, references column aliases down 4 levels, returns expected values without complaint.   I'm trying to understand why Oracle responds to the subqueries in the two sections differently.  If I can get clarity on that, I'm hoping it will help me figure out a way to work around the issue and build the query to completion.

                 

                Regards,

                Paul

                • 5. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                  odie_63

                  PK_UpNorth wrote:

                   

                  I'm trying to understand why Oracle responds to the subqueries in the two sections differently.

                  The two sections use different kinds of nested subqueries.

                   

                  To put in simple terms -

                  - 1st one is nesting subqueries in the SELECT clause :

                  select (select (select ...) ...)
                  from q0

                  That one has apparently no limitation, or perhaps a system hard limit (I don't know it off the top of my head but it's probably documented).

                  In this situation, column values from query q0 are passed as bind variables to the nested SELECTs.

                   

                  - 2nd one is nesting subqueries in the FROM clause :

                  select
                  from (
                    select
                    from (
                      select
                      from
                      where ...
                    )
                  )

                  In this situation, a given subquery can only be correlated to its nearest parent (one-level deep).

                  • 6. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                    PK_UpNorth

                    Thank you for the clarification, odie_63.  This helps me understand the issue and the disparity in the performance of the 2 sections. 

                     

                    In the second section, I'm just looking for the content of the C47TEXT column(s), but I need the C47TEXT returned values ordered by the C47NID and C47DSEQ fields.  I don't want the C47NID and C47DSEQ values showing up in the output results. It seems the positional notation, rather than the column names, is required in the subquery order by clause. In this case, is there an alternate way to use the C47NID and C47DSEQ values in an order by clause without selecting them so that I can use their positional notation?  That way I could move the subquery up one level.

                     

                    Does anyone see another approach off hand to resolving this in its current form before I have to go back and redesign it, fragmenting the query?

                     

                    Thanks,

                    Paul

                    • 7. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                      odie_63

                      Does anyone see another approach off hand to resolving this in its current form before I have to go back and redesign it, fragmenting the query?

                      If you can upgrade, starting from Oracle 12.1, it's now possible to correlate a nested subquery to a parent more than one level above.

                       

                      If you're stuck with 11.2.0.4 at the moment, I don't see many options (short of refactoring of course).

                       

                      Here's a suggestion, but it has a few drawbacks that may impact performance.

                      I reproduced your scenario on a smaller example :

                       

                      Original query :

                      SQL> select d.dname
                        2       , (
                        3           select xmlagg(xmlelement("name",ename)) 
                        4           from (
                        5             select e.ename, 1 as seq
                        6             from scott.emp e
                        7             where e.deptno = d.deptno
                        8             union
                        9             select 'XXXXX', 0
                       10             from dual
                       11             order by seq
                       12           )
                       13         ) as xml_names
                       14  from scott.dept d;
                                 where e.deptno = d.deptno
                                                  *
                      ERROR at line 7:
                      ORA-00904: "D"."DEPTNO": invalid identifier
                      

                       

                      OK, expected error.

                       

                      Now let's replace the innermost subquery with a cursor expression that we'll pass to the XMLType constructor.

                      The output will be a canonical XML document of this form :

                      <ROWSET>
                       <ROW>
                        <name>XXXXX</name>
                        <SEQ>0</SEQ>
                       </ROW>
                       <ROW>
                        <name>CLARK</name>
                        <SEQ>1</SEQ>
                       </ROW>
                      </ROWSET>
                      

                       

                      Then we just have to pass that again to XMLQuery function to extract the fragments we're interested in :

                       

                      SQL> select d.dname
                        2       , xmlquery('/ROWSET/ROW/name'
                        3           passing xmltype(
                        4                     cursor (
                        5                       select e.ename as "name", 1 as seq
                        6                       from scott.emp e
                        7                       where e.deptno = d.deptno
                        8                       union
                        9                       select 'XXXXX', 0
                       10                       from dual
                       11                       order by seq
                       12                     )
                       13                   )
                       14           returning content
                       15         ) as xml_names
                       16  from scott.dept d;
                      
                      DNAME          XML_NAMES
                      -------------- --------------------------------------------------------------------------------
                      ACCOUNTING     <name>XXXXX</name><name>CLARK</name><name>KING</name><name>MILLER</name>
                      RESEARCH       <name>XXXXX</name><name>ADAMS</name><name>FORD</name><name>JONES</name><name>SCO
                      SALES          <name>XXXXX</name><name>ALLEN</name><name>BLAKE</name><name>JAMES</name><name>MA
                      OPERATIONS     <name>XXXXX</name>
                      

                       

                      It works, but this approach introduces some overhead :

                      - opening a nested cursor

                      - context-switching

                      - call to the XQuery engine

                       

                      Let me know if it's a viable alternative for you.

                       

                       

                      BTW, when using XMLAGG to aggregate XML fragments in a specific order, it's recommended to use XMLAGG's own ORDER-BY clause, instead of relying on the ORDER-BY clause of the subquery  :

                      XMLAgg( XMLElement("xxxx", ...) ORDER BY ... )

                      Maybe you'll see no difference when XMLAgg is applied on a single group (as in the present example), but it's necessary to use it in a GROUP-BY query, if aggregation order is important.

                      • 8. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                        PK_UpNorth

                        odie_63,

                             Thanks for the time and suggestion.  We will be upgrading to Oracle version 12; unfortunately, I don't know what the time frame for that is and this project is immediate.  Before I go back to the drawing board with this, I'll take a look at the alternative approach, using the cursor expression, that you suggested.  In the grand scheme of things, the invoices are not very large; the overhead involved and the performance hit may be negligible. If it turns out to be viable, I'll update this thread.   Thank you for the pointer on the XMLAgg Order-By clause usage.

                         

                        Regards,

                        Paul

                        • 9. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                          PK_UpNorth

                          odie_63,

                            Thanks again for the help and time.  I ended up reworking this and using pipelined table functions.  I wrote a pipelined table function that took the aliased columns as input parameters.  I then substituted the call to the table function in place of the section with the correlated subqueries.  That took care of the issue.  I appreciate that you pointed me in that direction.

                           

                            I have another, simpler question now.  I can take it to its own question thread if that's needed.  The output of this query is an invoice, which, in its rough development stages, I'm spooling to a file.  The invoice may contain multiple orders, which may then contain multiple order lines. The entire query executes once for each order in the invoice.  This is controlled by an outer PLSQL cursor loop. Consequently, this query ends up outputting several thousand lines of XML.  I'm currently concatenating the results of each query into an XMLTYPE variable.  Then, when the loop is finished, I'm serializing the XML into a CLOB and outputting the contents of the CLOB in chunks using DBMS_OUTPUT with:

                           

                          SELECT XMLSERIALIZE(CONTENT FullDoc AS CLOB INDENT SIZE = 2) INTO ClobDoc FROM DUAL;
                          DocLength := DBMS_LOB.GETLENGTH(ClobDoc);
                          LoopVar := 1;
                          LOOP
                            DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(ClobDoc,8000,LoopVar));
                            LoopVar := LoopVar+8000;
                            EXIT WHEN LoopVar > DocLength;
                          END LOOP;
                          
                          

                           

                          This isn't very pretty and leaves a lot to be desired - it's not something I'd want to provide a customer.  However, I don't find Oracle's documentation for output of XML to file to be very informative, and the DBMS_OUTPUT method seems to be the one I come across when searching the web.  Do you have any suggestions for alternate, more efficient and better looking output of queries in XML?

                           

                          Regards and Thanks Again,

                          Paul

                          • 10. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                            odie_63

                            PK_UpNorth wrote:

                             

                            However, I don't find Oracle's documentation for output of XML to file to be very informative, and the DBMS_OUTPUT method seems to be the one I come across when searching the web. Do you have any suggestions for alternate, more efficient and better looking output of queries in XML?

                            You can write the CLOB content to a file very easily using DBMS_XSLPROCESSOR.CLOB2FILE procedure.

                            • 11. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                              Paulzip

                              odie_63 wrote:

                               

                              1- Minimize usage of XMLForest function.

                              Although it seems handy when dealing with NULLs, I almost never use it, except for generating leaf nodes out of simple expressions (i.e. not entire subqueries).

                              I'm intrigued to know why you suggest this, can you elaborate further?

                              • 12. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                                odie_63

                                Paulzip wrote:

                                 

                                odie_63 wrote:

                                 

                                1- Minimize usage of XMLForest function.

                                Although it seems handy when dealing with NULLs, I almost never use it, except for generating leaf nodes out of simple expressions (i.e. not entire subqueries).

                                I'm intrigued to know why you suggest this, can you elaborate further?

                                Based on my past experiences with XMLForest, I can tell you that using it from something else than generating leaf nodes (from scalar values) is not a good idea.

                                My comment was more directed towards the usage of nested XMLFORESTs, which can be harder to maintain when dealing with complexTypes.

                                 

                                XMLFOREST also used to have (and still has) bugs, at least last time I checked on 12.1.0.2.

                                 

                                And up to 11.2.0.4, XMLFOREST was just a "macro", i.e.

                                XMLForest(expr as "alias")

                                was internally evaluated as :

                                case when expr is not null then XMLElement("alias", expr) end

                                which may cause performance issues if <expr> is a complex expression.

                                But that's no longer true in 12c. The expression is now evaluated only once (or so it seems).

                                 

                                Below are a few examples gathered in this forum illustrating my point :

                                XMLFOREST: Generate Grand Parent tag using nested XMLFOREST

                                XMLFOREST with EVALNAME problem

                                XMLForest with multiple sub-queries not returning certain tags

                                • 13. Re: SQL/XML Functions Working With Column Aliases, and Correlated Subqueries
                                  PK_UpNorth

                                  Odie_63,

                                         Thanks for the follow up.  I'll look into the DBMS_XSLPROCESSOR.CLOB2FILE procedure for better output.

                                   

                                  Regards,

                                  Paul