Archives of the TeradataForum
Message Posted: Fri, 25 Sep 2015 @ 14:27:01 GMT
Thank you Marek for views.
Actually we have similar issue with the recursive query but not the same scenario.
For example, we have some 300,000 Employee defects (full_pr_num)under different user. As per hierarchy, we have requirement to assign all these defect to top hierarchy.
So each defect under his reportee will be passed on his mangers and so on.
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)) --- this is where we have the issue if we change it CLOB(500000) as it cannot be Hashed , 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 thoughts.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|