Kelvin Sparks
asked on
sys_connect_by_path subquery
Hi,
I'm not a strong Oracle SQL coder and have a query that is taking far too long to run. What I need is for it as one of the columns to return a concatenated list of values from another table. In brief I have the following:
SELECT
a, b, c,
(select ltrim(max(sys_connect_by_p ath(rpt_nu m,',')), ',')
from (select occ, rpt_num, row_number() over (partition by occ order by rownum) rn from oq)
connect by occ = prior occand rn = prior rn + 1
START WITH rn = 1
group by occ
HAVING occ= o.occ) Related_Reports
FROM TableO o
a,b, and c are other columns in my query
I suspect that I have messed something up in this, although running the sunquery as a query does return me what I need.
Any suggests or help is most welcome.
Kelvin
I'm not a strong Oracle SQL coder and have a query that is taking far too long to run. What I need is for it as one of the columns to return a concatenated list of values from another table. In brief I have the following:
SELECT
a, b, c,
(select ltrim(max(sys_connect_by_p
from (select occ, rpt_num, row_number() over (partition by occ order by rownum) rn from oq)
connect by occ = prior occand rn = prior rn + 1
START WITH rn = 1
group by occ
HAVING occ= o.occ) Related_Reports
FROM TableO o
a,b, and c are other columns in my query
I suspect that I have messed something up in this, although running the sunquery as a query does return me what I need.
Any suggests or help is most welcome.
Kelvin
ASKER
Thanks
Starting from the end - developing with 11.2.0.4, some testing with 11.2.0.3. Will be shipped to customers who could be on varying versions from 10.2.0.3 or later. Our next release will also include 12c (don't know which patch version yet.
Some raw data
Table TableO
Occ (PK) Date OccTitle
O14-1 25-01-2014 Title 1
O14-2 27-01-2014 Title 2
O13-25 15-11-2013 Title 3
Table oq
Occ rpt_num
O14-1 ABC123
O14-1 DEF986
O14-1 AA1234
O14-2 BB123
O14-2 ADBR12
Output desired
Occ Date OccTitle Related_Reports
O14-1 25-01-2014 Title 1 ABC123,DEF986,AA1234
O14-2 27-01-2014 Title 2 BB123,ADBR12
O13-25 15-11-2013 Title 3 '' (zero length string)
Thanks
Kelvin
Starting from the end - developing with 11.2.0.4, some testing with 11.2.0.3. Will be shipped to customers who could be on varying versions from 10.2.0.3 or later. Our next release will also include 12c (don't know which patch version yet.
Some raw data
Table TableO
Occ (PK) Date OccTitle
O14-1 25-01-2014 Title 1
O14-2 27-01-2014 Title 2
O13-25 15-11-2013 Title 3
Table oq
Occ rpt_num
O14-1 ABC123
O14-1 DEF986
O14-1 AA1234
O14-2 BB123
O14-2 ADBR12
Output desired
Occ Date OccTitle Related_Reports
O14-1 25-01-2014 Title 1 ABC123,DEF986,AA1234
O14-2 27-01-2014 Title 2 BB123,ADBR12
O13-25 15-11-2013 Title 3 '' (zero length string)
Thanks
Kelvin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
t is very unlikely to ever come near 4000 char
t is very unlikely to ever come near 4000 char
The XML version:
SELECT o.occ,
to_char(o.somedate,'DD-MM-YYYY') mydate,
o.occtitle,
rtrim(
extract(xmlagg(xmlelement("s", q.rpt_num || ',') order by rpt_num), '/s/text()').getclobval(),
','
)
related_reports
from tableo o
left outer join oq q on o.occ=q.occ
group by o.occ,
to_char(o.somedate,'DD-MM-YYYY'),
o.occtitle
/
ASKER
By adapting this slightly, I was able to reduce query overhead by 90%. SQL Monitoring tool has now identified a similar expression that I can work on. As always, thanks for your prompt assistance.
Kelvin
Kelvin
Also full database version (all 4 numbers like 11.2.0.2).