recursive table

Database
KVB
Enthusiast

recursive table

Hi,

 I have the below logic.My doubt is

Should I have the control in the second part of the UNION ALL statement to end the recursive logic or automatically it gets stopped once there is no condition satisfies the logic?

CREATE TABLE TABLE AS (

 WITH RECURSIVE TABLE_G (MERGE_ID, REM_ID, DW_EFF_DT, FIRST_MERGE_DT, CURR_MERGE_DT, HIER,LEVL)

 AS

 --SEED QUERY

 (SEL MERGE_ID,MERGE_ID, DW_EFF_DT, FIRST_MERGE_DT, CURR_MERGE_DT,CAST('\' AS VARCHAR(100)) AS HIER,CAST(1 AS DECIMAL(32,0)) AS LEVL  FROM MERGE_T

 WHERE MERGE_ID=0

UNION ALL

 SEL A.MERGE_ID,B.REM_ID, B.DW_EFF_DT, A.FIRST_MERGE_DT,

 CASE WHEN B.CURR_MERGE_DT= '9999-12-31' THEN A.CURR_MERGE_DT ELSE B.CURR_MERGE_DT END AS

  CURR_MERGE_DT,

   B.HIERARCHY||B.MERGE_ID||'\' AS HIER, B.LEVL+1

 FROM  MERGE_T  A INNER JOIN TABLE_G B

 ON A.REM_ID = B.MERGE_ID

 )

SELECT * FROM TABLE_G

) WITH DATA

3 REPLIES
Enthusiast

Re: recursive table

Hi Bikky,

I am not sure what data do you have in in Merge_T table. Because it will depend on your data whether the recursion will stop after testing the condition and stop at some point, or it goes into infnite recursion. I think if you know the levels of hirarchy then it would be better to test the level condition.

Can we see 10 rows sample data here?

Khurram
KVB
Enthusiast

Re: recursive table

This is the output of my recursive query.My strong point is

Is it necessary to give any control like LEVL<10 like that or Will it stop based on the data.Please give me clarity.

MERGE_ID REM__ID  dw_eff_dt first__MERGE_DT curr_merge_dt Hierarchy   LEVL

 1 935275 935275 2013-11-22 9999-12-31 9999-12-31 \    1

 2 447173 935275 2013-11-22 2013-11-22 2013-11-22 \  935275\   2

 3 25085 935275 2013-11-22 2013-11-22 2013-11-22 \  935275\  447173\ 3

Enthusiast

Re: recursive table

Bikky,

As you do not have any other condition except the join condition. on the basis of information you provided, I belive you should add WHERE LEVL<10 to avoid any unexpected recursion.

Khurram