I use below query to retrive a tall table:
with vt1 as
(select distinct t.id, s.step_name, ts.SEQ_NO
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:
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
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
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
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?
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.