Need help to write Recursive Query with Unknown Depth level

Database
Enthusiast

Need help to write Recursive Query with Unknown Depth level

Hi All,

I was writing recursive query for the first time. I need help in below recursive query in which I dont know the depth level.

Could anyone of you help me in solving this.

Scenario:

When SURV_ECI of A record is present as NON_SURV_ECI in B record,

Then SURV_ECI of A record will be updated to the SURV_ECI of B record ( only if SURV_ECI of B record is not present as NON_SURV_ECI in some other record).

If SURV_ECI of B record is present as NON_SURV_ECI in C record, then SURV_ECI of A record will be updated to the SURV_ECI of C record and  so on.

 Sample Data:

 SURV_ECID_NB     NON_SURV_ECID_NB

 0395784573          0395784581  ---A

 0395783976          0395784573  ---B

 0395784407          0395783976

 0037229708          0395784407

 Required Result:

  SURV_ECID_NB     NON_SURV_ECID_NB

 0037229708           0395784581

Rcursive query used:

 WITH RECURSIVE RECTBL(SURV_ECID_NB, NON_SURV_ECID_NB,LVL)

AS

(

SELECT SURV_ECID_NB, NON_SURV_ECID_NB, 1(INTEGER)

FROM ICDW_XLOB_PRSN_W.ENTIX_CONSLD_XREF_WK where NON_SURV_ECID_NB='0395784581'

UNION ALL

SELECT ENTIX_CONSLD_XREF_WK.SURV_ECID_NB, RECTBL.NON_SURV_ECID_NB, RECTBL.LVL + 1

FROM RECTBL INNER  JOIN ICDW_XLOB_PRSN_W.ENTIX_CONSLD_XREF_WK

ON RECTBL.SURV_ECID_NB= ENTIX_CONSLD_XREF_WK.NON_SURV_ECID_NB

AND RECTBL.LVL <= 100

)

SELECT SURV_ECID_NB, NON_SURV_ECID_NB,LVL

FROM RECTBL

QUALIFY ROW_NUMBER() OVER(PARTITION BY NON_SURV_ECID_NB ORDER BY LVL desc)  =1

Is there anyway that I can avoid used filter RECTBL.LVL <= 100 ???

Please help me to solve this

2 REPLIES
Junior Contributor

Re: Need help to write Recursive Query with Unknown Depth level

If you know there are no loops in your data you can simply remove it.

But I would never trust my data quality, so better increase it to a very high number you'll never reach, e.g. 10000.

Enthusiast

Re: Need help to write Recursive Query with Unknown Depth level

Hi Dieter
thank you for thr quick response..
if we make the limit to 10000 or something, query is running fr more time and spooling out..

is there any thing that we can do to avoid spoolspace issue...

please help me..