WITH RECURSIVE RECTBL(child_ID, child_nm, Parent_ID, Parent_NM, LVL)AS( SELECT CHILDid, childname, PARENTID, parentname, 0 AS lvl FROM WDM_S_WCIS_GEN_S1.HIERARCHY WHERE CHILDid = 1AND SNAP_EFF_DT = CURRENT_DATE - 1UNION ALL SELECT CHILDid, childname, PARENTid, parentname,RECTBL.LVL + 1 FROM RECTBLINNER JOIN HIERARCHY ON PARENT_ID = CHILDid AND child_id<>PARENT_ID)SELECT *FROM RECTBL AORDER BY a.LVL;
please help me figure out how to buld tabe I can pulld all parents for one customer
I used code below to find all parents for one customer child.
ideally result table should have 3 columns Customer ID , Parent ID and Level of ownership (1 - direct owner)
Customer Parent Lvl
A1 B 1
A1 C 2
A1 D 3
A2 C 1
A2 B 2
Thank you for your help!
rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.