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.
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.
0395784573 0395784581 ---A
0395783976 0395784573 ---B
Rcursive query used:
WITH RECURSIVE RECTBL(SURV_ECID_NB, NON_SURV_ECID_NB,LVL)
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'
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
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
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.