| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-29-11, 11:36
|
|
Registered User
|
|
Join Date: Aug 2011
Posts: 8
|
|
|
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
|
|
08-29-11, 19:12
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
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.
|
|
08-29-11, 19:28
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
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.
|
|
08-29-11, 19:39
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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
|
|
08-30-11, 05:09
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 09:01.
Reason: Specify column names explicitly in outmost SELECT clause to reorder the columns.
|
08-30-11, 09:36
|
|
Registered User
|
|
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.)
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Desperately need help with xml agg
