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)
(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
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
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
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?
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
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.