All Forums Database
04 Jul 2014
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
 

dnoeth 4628 posts Joined 11/04
05 Jul 2014

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.

Dieter

07 Jul 2014

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..

You must sign in to leave a comment.