Recursive Query

Database
Junior Supporter

Re: Recursive Query

Sarah,

So you main target is to get the latest_DND, Does this means that a DND goes through diffrent values? like at start it is 105 and at the current  value is 4008. from this data, I assume that rec1 is representing the latest dnd, and dnd_no is repsenting the initial DND. If you only have to get the latest DND then why dont you use max window function on date and get the DND at max date?

Does this makes sense?

Khurram
Enthusiast

Re: Recursive Query

Hi Khurram,

The actual condition is, for a particular DND value if the correspoding rec1 value is 0 then latest_dnd should be dnd only. Consider the set of values i have provided as sample.

For the record, dnd_no =100 the corresponding rec1 =200. So this time it searches for dnd_no = 200 and the corresponding rec1=300. again it searches for 300 in dnd column and the corresponding rec1=0, So the latest_dnd would be 300 for the record 100.

It should keep on seraching the column dnd to find its corresponding rec1 = 0. Then it stops there and should fetch the result where the dnd is becoming as zero. Hope you understood what i said here.

Ambassador

Re: Recursive Query

Hi Sarah,

assuming your actual data is not based on increasing values you need to do a recursion starting with the LATEST_DND:

WITH RECURSIVE cte(DND_NO, REC1, LATEST_DND) AS
(
SELECT DND_NO, REC1, DND_NO AS LATEST_DND
FROM tab
WHERE REC1 = 0

UNION ALL

SELECT t2.DND_NO, t2.REC1, cte.LATEST_DND
FROM cte JOIN tab AS t2
ON t2.REC1 = cte.DND_NO
)
SELECT * FROM cte;

What are you trying to calculate here:

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

What's the datatype of A.ACT_DATE?

Enthusiast

Re: Recursive Query

Hi Dieter,

Thanks for the suggestion.

The data type for A.ACT_DATE is VARCHAR. I don't know why they have defined a date column as varchar field. But we have to code according to that :)