How to integrate 2 separated queries into 1

Analytics
Enthusiast

How to integrate 2 separated queries into 1

I use below query to retrive a tall table: 

with vt1 as 
(select distinct t.id, s.step_name, ts.SEQ_NO
from t
inner join ts on ts.id = t.id
inner join s on s.OID = ts.OID
where t.ID in ('L85AAA1600', 'L83ABA1800'))

select distinct oth.id, p1.step_name as photostep,
oth.step_name as priorstep from vt1 as p1
inner join vt1 as oth
on p1.step_name like any ('3010%','3001%','3000%','3120%')
and p1.TRAV_STEP_SEQ_NO >= oth.TRAV_STEP_SEQ_NO
where photostep is not null
order by oth.trav_oid, photostep, oth.TRAV_STEP_SEQ_NO;

 

and I got the data format which is exactly what we want, example showing here:

Capture.PNG

It works well, but I'm wondering is that possible to merge all 'Priorstep' for particular id and photostep into single cell? 

what I try to do is based on above data table (name it by vt2) we got, run a seperated query as below

 

select id, photostep, 
TRIM(TRAILING ',' FROM (XMLAGG(priorstep || ','
ORDER BY priorstep) (VARCHAR(10000)))) as p_step from vt2
 
the example data format would be:
Capture1.PNG
 
now I have 2 seperated queries to get the final data I want, any way can integrate the 2 queries together as one round run? I have tried multipe way but failed.  thank you so much!
5 REPLIES
Junior Contributor

Re: How to integrate 2 separated queries into 1

You need to use multiple CTEs, before TD16 the logical order must be reversed, i.e. the 2nd must be defined before the 1st:

WITH vt2 AS 
 (
   SELECT DISTINCT oth.id, p1.step_name AS photostep,
   oth.step_name AS priorstep FROM vt1 AS p1
   INNER JOIN vt1 AS oth
   ON p1.step_name LIKE ANY ('3010%','3001%','3000%','3120%')
   AND p1.TRAV_STEP_SEQ_NO >= oth.TRAV_STEP_SEQ_NO
   WHERE photostep IS NOT NULL
 )
,
vt1 AS 
 (
   SELECT DISTINCT t.id, s.step_name, ts.SEQ_NO
   FROM t
   INNER JOIN ts ON ts.id = t.id
   INNER JOIN s ON s.OID = ts.OID
   WHERE t.ID IN ('L85AAA1600', 'L83ABA1800')
 )
SELECT id, photostep, 
Trim(Trailing ',' FROM (XmlAgg(priorstep || ','
ORDER BY priorstep) (VARCHAR(10000)))) AS p_step 
FROM vt2

 

Enthusiast

Re: How to integrate 2 separated queries into 1

Understand the key is to reverse the CTEs order, thank you so much. 

However, when I run the query, it shows me below results message:

 

Executed as Single statement. Failed [9134 : HY000] Intermediate aggregate storage limit for XMLAgg has been exceeded during computation.
Elapsed time = 00:00:00.812

 

Any idea how to overcome this? thank you

Junior Contributor

Re: How to integrate 2 separated queries into 1

Did you iinclude a GROUP BY id, photostep?

How many rows exist for an (id, photostep) combination?

Enthusiast

Re: How to integrate 2 separated queries into 1

thanks for your quick response, I put the 'group by id, photostep' just behind 'order by', there are nearly 60,000 rows for each id, average 100 rows for eacj photostep, too many?

Junior Contributor

Re: How to integrate 2 separated queries into 1

Probably.

I don't know about the exact limit, but it's probably like 64kb. 

When you try to cast the priorstep to a CLOB, it's a bit larger.