Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    8

    Unanswered: Desperately need help with xml agg

    Hi all,

    I am not a "dba" although I play one. I have a db with a lot of many to many tables. I have created a simple query to demonstrate my problem and how I solved part of it by reading this forum.

    The query:
    SELECT CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE.NAME,
    CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDOWNER, CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID DELIVERYPROCESS,
    CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDPRACTIC EAREA, CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDD ELIVERYMODEL,
    CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDIN DUSTRY
    FROM CAPABILITIES.ENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER,
    CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS,
    CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA,
    CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY,
    CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL
    WHERE CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDENGAGE MENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID ENGAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDENGAGEM ENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDEN GAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDE NGAGEMENTTYPE


    The resulting data set of course contained multiple rows of data for each engagement type. Example: Delivery process table contained 3 values, industry table contained 6 values. I end up with 18 rows for the engagement type.

    So I used xml aggregate to condense this to one row but that results the delivery process and industry columns containing the same info repeated multiple times separated by a comma - which I can use js to get the unique values. Seemed workable.

    Now, however, with data coming into the db and all these "aggregated" columns, I keep needing to bump up the number of characters used in the xml aggregated code (for 43 engagement types), I now have to set the varchars to be 32000 characters to make it work. There will be lots more engagement types and this won't work.

    Here is the full query an ACTUAL dba put together for me that is failing - I modified the varchars so you can see. I have to believe there is a better way to write this query and eliminate the issue from the beginning. I will be glad to answer any questions or provide additional information if someone can give me some guidance... right now, I am screwed.

    select a.* , b.IDINDUSTRY,b.IDDELIVERYMODEL from (
    SELECT CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE,
    CAPABILITIES.ENGAGEMENTTYPE.NAME, CAPABILITIES.ENGAGEMENTTYPE.DESCRIPTION,
    CAPABILITIES.STATUS.NAME AS STATUS, CAPABILITIES.ENGAGEMENTTYPE.STATUSDATE,
    CAPABILITIES.SERVICELINE.NAME AS SERVICELINE, CAPABILITIES.SERVICELINE.IDSERVICELINE, BOWNER.NAME AS OWNER, BOWNER.IDOWNER, CAPABILITIES.WEBLINKS.TITLE,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.DELIVERYPROCESS.NAME))) AS VARCHAR (16000)), 3) AS DELIVERYPROCESS,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.PRACTICEAREA.NAME))) AS VARCHAR (16000)), 3) AS PRACTICEAREA,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.INDUSTRY.NAME))) AS VARCHAR (16000)), 3) AS INDUSTRY, SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.DELIVERYMODEL.NAME))) AS VARCHAR (32000)), 3) AS DELIVERYMODEL,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.OWNER.NAME))) AS VARCHAR (16000)), 3) AS CONTENTOWNER, SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.OWNER.IDOWNER))) AS VARCHAR (16000)), 3) AS CONTENTIDOWNER,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.DELIVERYPROCESS.IDDELIVERYPROCESS))) AS VARCHAR (16000)), 3) AS IDDELIVERYPROCESS,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.PRACTICEAREA.IDPRACTICEAREA))) AS VARCHAR (16000)), 3) AS IDPRACTICEAREA
    FROM CAPABILITIES.ENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER,
    CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS,
    CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA,
    CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY,
    CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL, CAPABILITIES.OWNER,
    CAPABILITIES.OWNER AS BOWNER, CAPABILITIES.DELIVERYMODEL,
    CAPABILITIES.DELIVERYPROCESS, CAPABILITIES.INDUSTRY,
    CAPABILITIES.PRACTICEAREA, CAPABILITIES.SERVICELINE,
    CAPABILITIES.WEBLINKS, CAPABILITIES.STATUS
    WHERE CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDENGAGE MENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDOWNER = CAPABILITIES.OWNER.IDOWNER
    AND CAPABILITIES.ENGAGEMENTTYPE.IDOWNER = BOWNER.IDOWNER
    AND CAPABILITIES.ENGAGEMENTTYPE.IDSERVICELINE = CAPABILITIES.SERVICELINE.IDSERVICELINE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDWEBLINK = CAPABILITIES.WEBLINKS.IDWEBLINK
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID ENGAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID DELIVERYPROCESS = CAPABILITIES.DELIVERYPROCESS.IDDELIVERYPROCESS
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDENGAGEM ENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDPRACTIC EAREA = CAPABILITIES.PRACTICEAREA.IDPRACTICEAREA
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDEN GAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDIN DUSTRY = CAPABILITIES.INDUSTRY.IDINDUSTRY
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDE NGAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDD ELIVERYMODEL = CAPABILITIES.DELIVERYMODEL.IDDELIVERYMODEL
    AND CAPABILITIES.ENGAGEMENTTYPE.IDSTATUS = CAPABILITIES.STATUS.IDSTATUS
    GROUP BY CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE.NAME,
    CAPABILITIES.ENGAGEMENTTYPE.DESCRIPTION, CAPABILITIES.STATUS.NAME,
    CAPABILITIES.ENGAGEMENTTYPE.STATUSDATE, CAPABILITIES.SERVICELINE.NAME, CAPABILITIES.SERVICELINE.IDSERVICELINE,
    BOWNER.NAME, BOWNER.IDOWNER, CAPABILITIES.WEBLINKS.TITLE )a,

    (
    SELECT CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.INDUSTRY.IDINDUSTRY))) AS VARCHAR (16000)), 3) AS IDINDUSTRY,
    SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', CAPABILITIES.DELIVERYMODEL.IDDELIVERYMODEL))) AS VARCHAR (32000)), 3) AS IDDELIVERYMODEL


    FROM CAPABILITIES.ENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER,
    CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS,
    CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA,
    CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY,
    CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL, CAPABILITIES.OWNER,
    CAPABILITIES.OWNER AS BOWNER, CAPABILITIES.DELIVERYMODEL,
    CAPABILITIES.DELIVERYPROCESS, CAPABILITIES.INDUSTRY,
    CAPABILITIES.PRACTICEAREA, CAPABILITIES.SERVICELINE,
    CAPABILITIES.WEBLINKS, CAPABILITIES.STATUS
    WHERE CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDENGAGE MENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER.IDOWNER = CAPABILITIES.OWNER.IDOWNER
    AND CAPABILITIES.ENGAGEMENTTYPE.IDOWNER = BOWNER.IDOWNER
    AND CAPABILITIES.ENGAGEMENTTYPE.IDSERVICELINE = CAPABILITIES.SERVICELINE.IDSERVICELINE
    AND CAPABILITIES.ENGAGEMENTTYPE.IDWEBLINK = CAPABILITIES.WEBLINKS.IDWEBLINK
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID ENGAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS.ID DELIVERYPROCESS = CAPABILITIES.DELIVERYPROCESS.IDDELIVERYPROCESS
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDENGAGEM ENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA.IDPRACTIC EAREA = CAPABILITIES.PRACTICEAREA.IDPRACTICEAREA
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDEN GAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY.IDIN DUSTRY = CAPABILITIES.INDUSTRY.IDINDUSTRY
    AND CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE = CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDE NGAGEMENTTYPE
    AND CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL.IDD ELIVERYMODEL = CAPABILITIES.DELIVERYMODEL.IDDELIVERYMODEL
    AND CAPABILITIES.ENGAGEMENTTYPE.IDSTATUS = CAPABILITIES.STATUS.IDSTATUS
    GROUP BY CAPABILITIES.ENGAGEMENTTYPE.IDENGAGEMENTTYPE, CAPABILITIES.ENGAGEMENTTYPE.NAME,
    CAPABILITIES.ENGAGEMENTTYPE.DESCRIPTION, CAPABILITIES.STATUS.NAME,
    CAPABILITIES.ENGAGEMENTTYPE.STATUSDATE, CAPABILITIES.SERVICELINE.NAME, CAPABILITIES.SERVICELINE.IDSERVICELINE,
    BOWNER.NAME, BOWNER.IDOWNER, CAPABILITIES.WEBLINKS.TITLE) b
    where a.IDENGAGEMENTTYPE= b.IDENGAGEMENTTYPE
    ORDER BY a.NAME

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The resulting data set of course contained multiple rows of data for each engagement type. Example: Delivery process table contained 3 values, industry table contained 6 values. I end up with 18 rows for the engagement type.
    Here is a solution for such queries.
    (See Example b.)

    Example a:
    2 female and 1 male for deptno = A00, resulting 2 rows.
    3 female and 2 male for deptno = E11, resulting 6 rows.
    0 female and 4 male for deptno = E21, resulting 4 rows.

    Note: LEFT JOINs were used to include deptno = E21 of which number of female was 0 in the result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT d.deptno
         , d.mgrno
         , dm.firstnme || ' ' || dm.lastname mgrname
         , ef.empno female
         , em.empno male
     FROM  department d
     INNER JOIN
           employee   dm
       ON  dm.empno = d.mgrno
     LEFT  OUTER JOIN
           employee   ef
       ON  ef.workdept = d.deptno
       AND ef.sex = 'F'
     LEFT  OUTER JOIN
           employee   em
       ON  em.workdept = d.deptno
       AND em.sex = 'M'
     WHERE d.deptno IN ('A00' , 'E11' , 'E21')
     ORDER BY
           d.deptno
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO MGRNO  MGRNAME                      FEMALE MALE  
    ------ ------ ---------------------------- ------ ------
    A00    000010 CHRISTINE HAAS               000010 000110
    A00    000010 CHRISTINE HAAS               000010 000120
    E11    000090 EILEEN HENDERSON             000090 000300
    E11    000090 EILEEN HENDERSON             000090 000290
    E11    000090 EILEEN HENDERSON             000280 000300
    E11    000090 EILEEN HENDERSON             000280 000290
    E11    000090 EILEEN HENDERSON             000310 000300
    E11    000090 EILEEN HENDERSON             000310 000290
    E21    000100 THEODORE SPENSER             -      000340
    E21    000100 THEODORE SPENSER             -      000320
    E21    000100 THEODORE SPENSER             -      000100
    E21    000100 THEODORE SPENSER             -      000330
    
      12 record(s) selected.
    Example b:
    2 female and 1 male for deptno = A00, resulting 2 rows.
    3 female and 2 male for deptno = E11, resulting 3 rows.
    0 female and 4 male for deptno = E21, resulting 4 rows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT d.deptno
         , d.mgrno
         , CASE COALESCE(ef.rn , em.rn)
           WHEN 1 THEN
                dm.firstnme || ' ' || dm.lastname
           ELSE ''
           END  AS mgrname
         , ef.empno female
         , em.empno male
     FROM  department d
     INNER JOIN
           employee   dm
       ON  dm.empno = d.mgrno
     LEFT  OUTER JOIN
    
           (SELECT empno
                 , workdept
                 , ROW_NUMBER() OVER(PARTITION BY workdept) AS rn
             FROM  employee
             WHERE sex = 'F'
           ) ef
     FULL  OUTER JOIN
           (SELECT empno
                 , workdept
                 , ROW_NUMBER() OVER(PARTITION BY workdept) AS rn
             FROM  employee
             WHERE sex = 'M'
           ) em
       ON  em.workdept = ef.workdept
       AND em.rn       = ef.rn
    
       ON  COALESCE(ef.workdept , em.workdept) = d.deptno
     WHERE
           d.deptno IN ('A00' , 'E11' , 'E21')
     ORDER BY
           d.deptno
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO MGRNO  MGRNAME                      FEMALE MALE  
    ------ ------ ---------------------------- ------ ------
    A00    000010 CHRISTINE HAAS               000010 000110
    A00    000010                              -      000120
    E11    000090 EILEEN HENDERSON             000090 000290
    E11    000090                              000280 000300
    E11    000090                              000310 -     
    E21    000100 THEODORE SPENSER             -      000100
    E21    000100                              -      000320
    E21    000100                              -      000330
    E21    000100                              -      000340
    
      9 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to aggregate the result,
    aggregate each tables in subqueries, like...

    Example c:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT d.deptno
         , d.mgrno
         , dm.firstnme || ' ' || dm.lastname mgrname
         , ef.female
         , em.male
     FROM  department d
     INNER JOIN
           employee   dm
       ON  dm.empno = d.mgrno
     LEFT  OUTER JOIN
           LATERAL
           (SELECT workdept
                 , SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', empno))) AS VARCHAR (30)), 3) AS female 
             FROM  employee ef
             WHERE ef.workdept = d.deptno
               AND ef.sex = 'F'
             GROUP BY
                   workdept
           ) ef
       ON  0=0
     LEFT  OUTER JOIN
           LATERAL
           (SELECT workdept
                 , SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', empno))) AS VARCHAR (40)), 3) AS male 
             FROM  employee em
             WHERE em.workdept = d.deptno
               AND em.sex = 'M'
             GROUP BY
                   workdept
           ) em
       ON  0=0
     WHERE d.deptno IN ('A00' , 'E11' , 'E21')
     ORDER BY
           d.deptno
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO MGRNO  MGRNAME                      FEMALE                         MALE                                    
    ------ ------ ---------------------------- ------------------------------ ----------------------------------------
    A00    000010 CHRISTINE HAAS               000010                         000110, 000120                          
    E11    000090 EILEEN HENDERSON             000310, 000280, 000090         000300, 000290                          
    E21    000100 THEODORE SPENSER             -                              000340, 000320, 000100, 000330          
    
      3 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please use correlation-name for tables, like...

    correlation-names: "et", "etc", "etd", so on.
    Code:
    SELECT et. IDENGAGEMENTTYPE
         , et. NAME
         , etc.IDOWNER
         , etd.ID DELIVERYPROCESS
         , etp.IDPRACTICEAREA
         , etu.IDD ELIVERYMODEL
         , ets.IDIN DUSTRY
     FROM  CAPABILITIES.ENGAGEMENTTYPE                     et
         , CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER       etc
         , CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS etd
         , CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA        etp
         , CAPABILITIES.ENGAGEMENTTYPE_SUPPORTS_INDUSTRY   ets
         , CAPABILITIES.ENGAGEMENTTYPE_USES_DELIVERYMODEL  etu
     WHERE et.IDENGAGEMENTTYPE = etc.IDENGAGE MENTTYPE
       AND et.IDENGAGEMENTTYPE = etd.ID ENGAGEMENTTYPE
       AND et.IDENGAGEMENTTYPE = etp.IDENGAGEM ENTTYPE
       AND et.IDENGAGEMENTTYPE = ets.IDEN GAGEMENTTYPE
       AND et.IDENGAGEMENTTYPE = etu.IDE NGAGEMENTTYPE

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this for your full query?

    Note 1: two subqueries were omitted.
    Note 2: VARCHAR (1600) might be too long.
    Code:
    SELECT IDENGAGEMENTTYPE
         , NAME
         , DESCRIPTION
         , STATUS
         , STATUSDATE
         , SERVICELINE
         , IDSERVICELINE
         , OWNER
         , IDOWNER
         , TITLE
         , DELIVERYPROCESS
         , PRACTICEAREA
    --     , INDUSTRY
    --     , DELIVERYMODEL
         , CONTENTOWNER
         , CONTENTIDOWNER
         , IDDELIVERYPROCESS
         , IDPRACTICEAREA
    --     , IDINDUSTRY
    --     , IDDELIVERYMODEL
     FROM  (SELECT et. IDENGAGEMENTTYPE
                 , et. NAME
                 , et. DESCRIPTION
                 , sts.NAME AS STATUS
                 , et. STATUSDATE
                 , sln.NAME AS SERVICELINE
                 , sln.IDSERVICELINE
                 , own.NAME AS OWNER
                 , own.IDOWNER
                 , wlk.TITLE
             FROM  CAPABILITIES.ENGAGEMENTTYPE                     et
                 , CAPABILITIES.OWNER                              own
                 , CAPABILITIES.SERVICELINE                        sln
                 , CAPABILITIES.WEBLINKS                           wlk
                 , CAPABILITIES.STATUS                             sts
             WHERE
                   et. IDOWNER           = own.IDOWNER
               AND et. IDSERVICELINE     = sln.IDSERVICELINE
               AND et. IDWEBLINK         = wlk.IDWEBLINK
               AND et. IDSTATUS          = sts.IDSTATUS
           ) et
         , LATERAL
           (SELECT SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', own.NAME             ))) AS VARCHAR (1600)), 3) AS CONTENTOWNER
                 , SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', own.IDOWNER          ))) AS VARCHAR (1600)), 3) AS CONTENTIDOWNER
             FROM  CAPABILITIES.ENGAGEMENTTYPE_CONTENT_OWNER       etow
                 , CAPABILITIES.OWNER                              own
             WHERE et.  IDENGAGEMENTTYPE  = etow.IDENGAGEMENTTYPE
               AND etow.IDOWNER           = own. IDOWNER
             GROUP BY
                   et.  IDENGAGEMENTTYPE
           ) cown
         , LATERAL
           (SELECT SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', dp. NAME             ))) AS VARCHAR (1600)), 3) AS DELIVERYPROCESS
                 , SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', dp. IDDELIVERYPROCESS))) AS VARCHAR (1600)), 3) AS IDDELIVERYPROCESS
             FROM  CAPABILITIES.ENGAGEMENTTYPE_HAS_DELIVERYPROCESS etdp
                 , CAPABILITIES.DELIVERYPROCESS                    dp
             WHERE et.  IDENGAGEMENTTYPE  = etdp.IDENGAGEMENTTYPE
               AND etdp.IDDELIVERYPROCESS = dp.  IDDELIVERYPROCESS
             GROUP BY
                   et.  IDENGAGEMENTTYPE
           ) dp
         , LATERAL
           (SELECT SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', pa. NAME             ))) AS VARCHAR (1600)), 3) AS PRACTICEAREA
                 , SUBSTR(XMLSERIALIZE(XMLAGG(XMLTEXT(CONCAT(', ', pa. IDPRACTICEAREA   ))) AS VARCHAR (1600)), 3) AS IDPRACTICEAREA
             FROM  CAPABILITIES.ENGAGEMENTTYPE_PRACTICEAREA        etpa
                 , CAPABILITIES.PRACTICEAREA                       pa
             WHERE et.  IDENGAGEMENTTYPE  = etpa.IDENGAGEMENTTYPE
               AND etpa.IDPRACTICEAREA    = pa.  IDPRACTICEAREA
             GROUP BY
                   et.  IDENGAGEMENTTYPE
           ) pa
    ...
    ...
     ORDER BY
           et.NAME  
    ;
    Last edited by tonkuma; 08-30-11 at 10:01. Reason: Specify column names explicitly in outmost SELECT clause to reorder the columns.

  6. #6
    Join Date
    Aug 2011
    Posts
    8
    Thank you so very much. I had thought if I could break it down into multiple queries, it might work but I had no idea how to do it. With your example, I modified the query adding in the couple of additional tables I needed and it worked like a charm. I even reduced the varchar from 1600 to 1024 and it worked fine. It solved several issues, reduced the size of the query itself, and allowed me to removed some functions in code that were necessary to modify the previous output to work within my application framework. Again, thank you so very much. (Now I need to modify a dozen other queries.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •