LOBs are not allowed to be hashed issue in Recursive query

Database

LOBs are not allowed to be hashed issue in Recursive query

Hi All,

WITH RECURSIVE   my_rec(usr_nm,full_pr_num,LVL)

  
AS

   (

   
SELECT usr_nm,full_pr_num (clob(50000)),CAST (1 AS BIGINT)

   
FROM vt_temp

    WHERE rn = 1

    UNION ALL

    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

   )

  
SELECT usr_nm,full_pr_num,LVL

   FROM my_rec

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

Regards,

John

4 REPLIES
Senior Apprentice

Re: LOBs are not allowed to be hashed issue in Recursive query

Hi John,

Do you really need a CLOB as result, what's the data type of full_pr_num and the maximum result size? 

Re: LOBs are not allowed to be hashed issue in Recursive query

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 (

 SELECT

   usr_nm                    

   ,full_pr_num                                          

   ,ROW_NUMBER() OVER (PARTITION BY usr_nm ORDER BY FULL_PR_NUM) AS rn

FROM  edw.bbi_bcklg_prs_reprt_dtl  

WHERE

   bbi_bcklg_prs_reprt_dtl.measured_flag='dev' AND

   dev_currnt_bcklg_ind='Y'

) WITH DATA PRIMARY INDEX(usr_nm) ON COMMIT PRESERVE ROWS;

 ---- recursive logic to concatenate diff rows to one based on user name

DEL FROM  EDW_TMP.bbi_recur_prs;

 INSERT INTO  EDW_TMP.bbi_recur_prs

 (

 User_Nm,

 PRS,

 LVL

 )

  WITH RECURSIVE   rec_test(usr_nm,full_pr_num,LVL)

   AS

   (

    SELECT usr_nm,full_pr_num (VARCHAR(63500)), CAST (1 AS BIGINT)

    FROM vt_temp

    WHERE rn = 1

    UNION ALL

    SELECT  rec_test.usr_nm, TRIM(vt_temp.full_pr_num) || '|' || rec_test.full_pr_num, LVL+1

    FROM vt_temp  INNER JOIN rec_test

    ON vt_temp.usr_nm = rec_test.usr_nm

   AND vt_temp.rn = rec_test.lvl+1

   )

   SELECT usr_nm,full_pr_num,LVL

   FROM rec_test

QUALIFY RANK() OVER(PARTITION BY usr_nm ORDER BY LVL DESC) = 1;

Since the values are getting truncated, now trying to find an alternate solution. Kindly share your throughts.

Regards,

John

Re: LOBs are not allowed to be hashed issue in Recursive query

Hi, I am facing the same problem. Can you put it over here if you were able to overcome the issue?

Re: LOBs are not allowed to be hashed issue in Recursive query

Hi Harshit,

This was a limitation. we dropped proceeding after this issue. 

Regards,

John