All Forums Database
Elka 3 posts Joined 01/14
06 Jun 2016
Find all parents for child
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 = 1
AND SNAP_EFF_DT = CURRENT_DATE - 1

UNION ALL 
SELECT CHILDid, childname, PARENTid,  parentname,
RECTBL.LVL + 1 
FROM RECTBL
INNER JOIN HIERARCHY  ON PARENT_ID = CHILDid 
AND child_id<>PARENT_ID
)
SELECT *
FROM RECTBL A
ORDER BY a.LVL;

Hi experts!
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!
Elena
 
 
 

Tags:
You must sign in to leave a comment.