WITH RECURSIVE my_rec(usr_nm,full_pr_num,LVL)
SELECT usr_nm,full_pr_num (clob(50000)),CAST (1 AS BIGINT)
WHERE rn = 1
SELECT my_rec.usr_nm, TRIM(vt_temp.full_pr_num) || '|' || my_rec.full_pr_num, LVL+1
FROM vt_temp INNER JOIN my_rec
ON vt_temp.usr_nm = my_rec.usr_nm
AND vt_temp.rn = my_rec.lvl+1
QUALIFY RANK() OVER(PARTITION BY usr_nm ORDER BY LVL DESC) = 1
Error: EXPLAIN Failed. 5690: LOBs are not allowed to be hashed.
How do I use CLOB data type here? The max LVL limit is more that 5000 and it is dynamic.
So not able to use traditional Contactenate method. Please share your thoughts
Do you really need a CLOB as result, what's the data type of full_pr_num and the maximum result size?
Thank you for your quick reply Dieter.
Actually I have a requirement to concatenate 300,000 full_pr_num(Varchar(30) records) which is under different user to one user(top hierarchy)
Below are steps which we follow now but values getting truncated as full_pr_num (VARCHAR(63500)) is not sufficient enough to accommodate all the rows.
Since hash issue arises when we try modifying the full_pr_num (VARCHAR(63500)) to CLOB(500000)
CREATE VOLATILE TABLE vt_temp AS (