Recursive Query

Database
Enthusiast

Recursive Query

Hi All,

Can you please let me how recursive query works for more than 2 tables. As per my understanding with the syntax, we can take only one source table and the other would be recursive table. My doubt is can we take more than 2 source tables? If so kindly let me know how to achieve it.

Tags (1)
13 REPLIES
Enthusiast

Re: Recursive Query

Hi Sarah,

Yes you can use more than on table in source using joins.

Syntax:

WITH RECURSIVE <query name> AS
(SELECT COL_list FROM A
INNER JOIN B
ON A.Col = B.Col
UNION ALL
<RECURSIVE STATEMENT>);
Khurram
Enthusiast

Re: Recursive Query

Hi Khurram,

My scenario is

CASE WHEN  ( B.REC1 IS NULL OR B.REC1 =0)
THEN LATST_DND --this was derived from other case stmt
else B.REC1 END AS LATESTDND

my sample data would like below

B.REC1 A.DND_NO

2239    104

4015    2239

0          4015

Now, for the record A.DND_NO ='104' my LATESTDND should be 4015. But as per my query the output stops at 2239 which is not the expected result. Am not able to figure out how to do it with recursive query. Kindly help me.

Enthusiast

Re: Recursive Query

Sarah,

Can you please paste your recursive query code? 

Khurram
Enthusiast

Re: Recursive Query

Khurram,

Actually I have to modify my code using recursive query.

This is my actual query which is not working as expected. So my friend suggested me to use recursive query which is new to me. So asking out for help.

INSERT INTO TEMP_REC1
SELECT PRD_END_DT,RECORD_ID,
CASE WHEN ACT_DATE='2020-12-31'
THEN '0' ELSE REC1
END AS REC1,
CASE WHEN (ACT_DATE='2020-12-31' OR
(SUBSTR(ACT_DATE,4,2) < EXTRACT(DAY FROM PRD_END_DT)) OR
(SUBSTR(ACT_DATE,4,2) = EXTRACT(DAY FROM PRD_END_DT) AND SUBSTR(ACT_DATE,1,2) <= EXTRACT(MONTH FROM PRD_END_DT)))
THEN 'Y' ELSE 'N' END AS ACT_CURR_IND
FROM ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP ;

SELECT
S.PRD_END_DT,
S.CC,
S.DND_NO,
S.LDND

FROM (
SELECT A.PRD_END_DT,A.CC,A.DND_NO,
CASE WHEN B.ACT_CURR_IND ='N'
THEN A.HOME_DND
ELSE a.DND_NO END AS HME_DND,

CASE WHEN ACT_CURR_IND='N'
THEN HME_DND
ELSE  CASE WHEN B.REC1 <> 0 THEN B.REC1
ELSE HME_DND END END AS LATST_DND,

CASE WHEN /*COALESCE(b.REC1,0) =0*/( B.REC1 IS NULL OR B.REC1 =0)
THEN LATST_DND
else B.REC1 END AS LDND

FROM ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP A
LEFT OUTER JOIN  ICDW_XLOB_PRSN_D9_W.TEMP_REC1 B
ON B.REC1=A.DND_NO
and a.DND_NO = HME_DND
AND A.DND_NO = LATST_DND
)S

 I have yet to start writing the code. Your help would be much appreciated.

Junior Contributor

Re: Recursive Query

Hi Sarah,

i don't know your actual business question, but looking at your source code i don't think you need neither recursion nor the TEMP_REC1 table. Seems like a job for OLAP-functions.

Could you describe your actual data in DND_XREF_TEMP and the expected result?

Dieter

Enthusiast

Re: Recursive Query

Hi Dieter,

The data in DND_XREF_TEMP looks like,

ACT_DATE                  REC1  CC         DND_NO

12/31/2020 0:00:00  0        594051 101

06/17/1999 0:00:00  250    762653 102

06/09/2006 0:00:00  4440  900630 103

12/30/1998 0:00:00  2239  280068 104

02/22/2007 0:00:00  4008  890724 105

and my output should be like,

ORIG_DND LATEST_DND

101            101

102            250

103            4440

104            4015

105            4008

The ultimate moto is get Latest_dnd.

Junior Contributor

Re: Recursive Query

Hi Sarah,

i can't see any rule how to calculate this data. 

Why is LATEST_DND the same as REC1, just for 104 it's 4015? 4015 doesn't seem to be based on any kind of calulation.

Is there any relationship between rows? Based on increasing DND_NO?

Enthusiast

Re: Recursive Query

Hi Dieter,

Actually this is a hierarchy method. Below are a sample data.

DND_NO REC1 LATEST_DND

100 200 300

200 300 300

300 0 300

400 500 800

500 600 800

600 700 800

700 800 800

800 0 800

I need to join DND_NO = REC1 to get the LATEST_DND. How can this be achieved through recursive query? Or is there any other method to do this? Please help.

Enthusiast

Re: Recursive Query

I have tried this using many left outer joins. But the hierarchy level wouldn't change for some records. Dont know how to overcome that issue. Any help would be much appreciated.

SELECT  A.PRD_END_DT,A.DND_NO,
CASE WHEN A.ACT_DATE='2020-12-31'
THEN '0' ELSE A.REC1
END AS DRVD_REC1,
CASE WHEN (A.ACT_DATE='2020-12-31' OR
(SUBSTR(A.ACT_DATE,4,2) < EXTRACT(DAY FROM A.PRD_END_DT)) OR
(SUBSTR(A.ACT_DATE,4,2) = EXTRACT(DAY FROM A.PRD_END_DT) AND SUBSTR(A.ACT_DATE,1,2) <= EXTRACT(MONTH FROM A.PRD_END_DT)))
THEN 'Y' ELSE 'N' END AS ACT_CURR_IND,
CASE WHEN ACT_CURR_IND ='N' THEN A.HOME_DND ELSE A.DND_NO END AS HME_DND,
CASE WHEN ACT_CURR_IND ='N'  THEN HME_DND ELSE 
CASE WHEN DRVD_REC1<>0 THEN DRVD_REC1 ELSE HME_DND END END AS LATEST_DND,
CASE WHEN (DRVD_REC1 IS NULL OR DRVD_REC1 = 0) THEN LATEST_DND ELSE DRVD_REC1 END AS LDND

FROM ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP A
LEFT OUTER JOIN ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP B
ON A.REC1=B.DND_NO
LEFT OUTER JOIN ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP C
ON B.REC1=C.DND_NO
LEFT OUTER JOIN ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP D
ON C.REC1=D.DND_NO
LEFT OUTER JOIN ICDW_XLOB_PRSN_D9_W.DND_XREF_TEMP E
ON D.REC1= E.DND_NO
ORDER BY A.DND_NO