I keep getting no more spool space error while using a recrusive query on a table which has like 24224872 rows. Is there any other better way to do it or how do i fix this? Thanks.
Can you give more details about the logic you are trying to implement ? Usually its about the logic that you are trying to implement.
Thanks for your help Irfan. Below is my sql -
CREATE MULTISET VOLATILE TABLE vt_temp AS
ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY state_id) AS rn
ON COMMIT PRESERVE ROWS;
create RECURSIVE view rec_test(CUST,state,LVL)
SELECT CUST_ID,state_ID (VARCHAR(1000)), 1
FROM vt_temp WHERE rn = 1 UNION ALL SELECT CUST_ID, TRIM(state_ID)
|| ', ' || state,LVL+1
FROM vt_temp INNER JOIN rec_test ON state_ID = ALLOC AND vt_temp.rn = rec_test.lvl+1 ) ;
FROM rec_test QUALIFY RANK() OVER(PARTITION BY CUST ORDER BY LVL DESC) = 1;
My table myud.vt_allocpct has 24224872 values .
Format is -
Expected output is -
Using recursion for this task will result in a spool file with an approximate size of
(COUNT(*) + 1) * SUM(CHAR_LENGTH(state)+1) / 2 for each cust_id
Is there a known maximum number of states per customer?
Then you better use the old approach concatenating lots of
MAX(CASE MAX(CASE WHEN rn = x THEN ',' || state_id ELSE '' END