Dynamic SQL to loop through SubID's until Valid Category is found.

Question

Step 1 : Get Parent and Child SubID's along with Parent Material and corresponding Components.

Step 2: Loop through each component and check if corresponding material is in category - RA,CC,PB grouped by Child SubID.

Step 3: If any of the of child SubId's do not contain the components then pass this new child SubID to Step 1 and get components and next level material numbers to check if this level of SubID's contain category until category - RA,CC,PB are found or no records found for the next level SubID's

: Sample Query and Snips :

1) Level 1: Parent SubID,Material and corresponding Components:

Select DISTINCT S1.Material_Number Parent_Material,   
       S1.SUBID Parent_SubId ,   
       S2.MATNR L1_Material,  
       S2.SUBID L1_SubId,  
       s2.RECNCMP L1_Child_Component   
      ,Material_Type  
      ,Material_Number  
      ,COMPAVG  
FROM   
(  
select * from Table1    
WHERE   SUBID   =   'BE50894'            
) DER1   
INNER JOIN Table2 va on va.RECNROOT = DER1.ESTVH_RECNROOT AND va.RECNTVH = Der1.ESTVH_RECN   
INNER JOIN Table3 vp on va.RECN = vp.RECNTVA   
) S1  LEFT JOIN   
(   
SELECT   
                    rh.RECNROOT  rh_recnroot   
                    ,vh.RECN vh_recn  
                    ,rh.RECN Rh_Recn  
                    ,MJ.ACTN  
                    ,vh.ESTCAT  
                    ,rh.SUBID  
                    ,mj.MATNR     
                    ,mj.WERKS  
                    ,vp.RECNCMP  
                FROM  Table1 rh   
                    INNER JOIN Table2 vh  ON rh.RECN = vh.RECNROOT AND vh.ESTCAT = 'Z_PLM_PBAU_COMP'  
                    INNER JOIN Table3 mj  ON rh.RECN = mj.RECNROOT  
                    INNER JOIN Table4 va on va.RECNROOT = vh.RECNROOT AND va.RECNTVH = vh.RECN   
                    INNER JOIN Table5 vp on va.RECN = vp.RECNTVA   

) S2  ON S1.RECNCMP = S2.rh_recnroot  

Above code will give base data to work with there we need to check materials for SUBID = 'BE50894'

Output:

Level 2:

SELECT distinct
L.*
,mj.MATNR L2_Material
,rh.SUBID L2_SubId
, vp.RECNCMP L2_Child_Component
,Ma.MTART L2_Material_Type
FROM #level1 l
LEFT JOIN Table1 rh ON l.L1_Child_Component = rh.RECN
INNER JOIN Table2 vh ON rh.RECN = vh.RECNROOT AND vh.ESTCAT = 'Z_PLM_PBAU_COMP'
INNER JOIN Table3 mj ON rh.RECN = mj.RECNROOT
INNER JOIN Table4 va on va.RECNROOT = vh.RECNROOT AND va.RECNTVH = vh.RECN
INNER JOIN Table5 vp on va.RECN = vp.RECNTVA
INNER JOIN Table6 Ma ON Ma.MATNR = mj.MATNR
WHERE mj.DELFLG = ''
AND mj.VALTO = 99991231

This stage , join the result from Level 1 using Child component to detect Material type for corresponding L2_SubId .. if L2_SubID is not in RA,CC,PB then take this SubID and loop in level 1 until a corresponding match or Blank record is found.

Level 2: