question

vishalchitrala-8227 avatar image
0 Votes"
vishalchitrala-8227 asked EchoLiu-msft commented

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


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 :
115473-parentlevel1.png

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:

115419-level2.png


sql-server-generalsql-server-transact-sqlazure-sql-database
parentlevel1.png (80.7 KiB)
level2.png (86.8 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There is something in the language of this question which makes me think that this is an assignment or an interview question. Personally, I'm less inclined to answer such questions, but I will point out two things.

The first is that the standard recommendation for this question is that you supply CREATE TABLE statements for your tables + INSERT statements with test data, enough to illustrate all angles of the problem. We also like to see the expected results given the test data. This makes it simple to copy and paste into a query window. Without that input, you are more likely to get a piece of guesswork just typed up without checking. This can be a frustrating experience for everyone.

The other thing I like to say is that I cannot see any need for dynamic SQL here. But maybe I am missing something?

1 Vote 1 ·

No, It is not an assignment or Interview to begin with .Unfortunately this is how SAP stored the data. I tried with a solution using Cursor but it's at level 2 where I need to loop through to get subsequent SubId's is where Cursor fails. I was thinking something in terms or recursive query or dynamic SQL could achieve what I was looking for. Thank you for suggestion , will edit my query with screen shots.

0 Votes 0 ·

So did you read this part of my post:

The first is that the standard recommendation for this question is that you supply CREATE TABLE statements for your tables + INSERT statements with test data, enough to illustrate all angles of the problem. We also like to see the expected results given the test data. This makes it simple to copy and paste into a query window. Without that input, you are more likely to get a piece of guesswork just typed up without checking. This can be a frustrating experience for everyone.

1 Vote 1 ·

Hi @Hi @vishalchitrala-8227,

As ErlandSommarskog said, we need you to provide the corresponding tables and test data. Your code involves at least table1, table2, table3, table4, table5, table6, and #level1 tables. But from the screenshot you provided, we can only find information about table1.

In addition, I also think that static SQL seems to be able to solve your problem. However, due to lack of tables and data, we cannot provide a solution for the time being.

Regards
Echo

0 Votes 0 ·

Could you have any update?Did the following methods solve your problem?If not, please provide more details.

0 Votes 0 ·

0 Answers