Archives of the TeradataForum

Message Posted: Fri, 25 Sep 2015 @ 14:27:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

Regards,

John

	<Prev	Next>		<<First	<Prev	Next>	Last>>