Beas Item Structure QUERY

• 

  Martin Heigl

  • April 19, 2024 07:59

  for this you need a recursive function. I think, this is not possible in SQL

  With beas script command

  setvar=itemcode="FT"

  object=ue_artikelstamm=structureexport=itemcode=<itemcode><tab>
  tools=isql=select * from "BEAS_REPORTAUSWERTUNG" where "ID"=<id> order by "NR"

  the system create the report and save this in the table
  this command support additional parameters as
  
  maxlevel, routingid, inactive, dateid, version, expandpoitems and quantity
  example
  
  object=ue_artikelstamm=structureexport=itemcode=FT<tab>quantity=10
  
  

   

  0
• 

  SEETHA

  • April 22, 2024 07:31

  Hi, But the BEAS_REPORTAUSWERTUNG  first day save next day no the data empty in table. How do i apply from BEAS_REPORTAUSWERTUNG. Got any other way to to get exact data from ITEM STRUCTURE.Or any other table to get exact item structure.

  0
• 

  SEETHA

  • April 22, 2024 07:44

  Hi, BEAS_STL and OITM can match recursive item structure.

  0
• 

  SEETHA

  • April 22, 2024 07:55

  Hi, this i take from BEAS_REPORTAUSWERTUNG.  BEAS_REPORTAUSWERTUNG is just temporary only. The next day is empty column. May i know which table and which column i take exact column from here.

  0
• 

  SEETHA

  • April 22, 2024 07:57

  Hi, May i know how to do recursive function.

  0
• 

  SEETHA

  • April 22, 2024 08:28

  Hi, Can give me some example?

  0
• 

  SEETHA

  • April 22, 2024 08:36

  Hi, i use the beas in hana.

   

  0
• 

  SEETHA

  • April 22, 2024 08:46

  Hi, from here BEAS_STL have less itemcode how do i match in child AN120FCA0_BP and AN120FCA0_WF

  

  0
• 

  SEETHA

  • April 22, 2024 08:53

  Hi, BEAS_REPORTAUSWERTUNG once save is there but now the data in table is empty after update 1am. How to solve it once save data should be there after 1am update should be there also. Currently after update 1am data is empty. Do have any solve method on this report table and i request many question can give me some idea then i try it.

  Thanks.

  0
• 

  SEETHA

  • April 22, 2024 09:38

  Hi, is this correct query. But why i got error when execute

  with Hierachy(art1_id, itemcode, Level)
  as
  (
  select art1_id, itemcode, 0 as Level
  from beas_stl c
  where c.art1_id = '266549'
  union all
  select c.art1_id, c.itemcode, ch.Level + 1
  from beas_stl c
  inner join Hierachy ch
  on c.itemcode = ch.art1_id
  )
  select art1_id, itemcode
  from Hierachy
  where Level > 0

  0
• 

  SEETHA

  • April 22, 2024 09:39

  Can help me on this also. Or if this not correct can give me other solution.

  0
• 

  João Antonio

  • April 22, 2024 11:02

  He,
  In SQL Server it is possible to make a recursive query using with and it contains some recursive parameters.
  We use it to create some reports and it works well.

  0
• 

  SEETHA

  • April 22, 2024 12:17

  Hi, how to get correct answer. I got an error. Can I have example.

  0
• 

  SEETHA

  • April 22, 2024 12:21

  Hi, when I create query recursive there is show an error. Hierarchy not create table. If I'm using with.

  0
• 

  SEETHA

  • April 22, 2024 12:22

  Hi, can anyone give correct answer.

  0
• 

  SEETHA

  • April 22, 2024 12:38

  Hi, I just try but error. Can anyone solve this.

  

  0
• 

  SEETHA

  • April 22, 2024 12:46

  Hi, Do anyone can solve BEAS_REPORTAUSWERTUNG query. I comment in up. You may find it up stairs.

  0
• 

  SEETHA

  • April 23, 2024 05:42

  Hi, Can anyone solve this error.

  

  0
• 

  SEETHA

  • April 25, 2024 06:42

  Hi, Please anyone help me on.

  0
• 

  SEETHA

  • April 25, 2024 06:46

  Hi Martin, Can help me on how to multilevel bom. 

  DECLARE @Father VARCHAR(max), @Father_Name VARCHAR(max),@Child VARCHAR(50),@Child_Name VARCHAR(max),@BOMType VARCHAR(50),@LEVEL INT,@Quantity as Float,@UOM as varchar(max),

  @Child1 VARCHAR(50),@Child_Name1 VARCHAR(max),@BOMType1 VARCHAR(50),@LEVEL1 INT,@Quantity1 as Float,@UOM1 as varchar(max),

  @Child2 VARCHAR(50),@Child_Name2 VARCHAR(max),@BOMType2 VARCHAR(50),@LEVEL2 INT,@Quantity2 as Float,@UOM2 as varchar(max),

  @Child3 VARCHAR(50),@Child_Name3 VARCHAR(max),@BOMType3 VARCHAR(50),@LEVEL3 INT,@Quantity3 as Float,@UOM3 as varchar(max),

  @Child4 VARCHAR(50),@Child_Name4 VARCHAR(max),@BOMType4 VARCHAR(50),@LEVEL4 INT,@Quantity4 as Float,@UOM4 as varchar(max),

  @Child5 VARCHAR(50),@Child_Name5 VARCHAR(max),@BOMType5 VARCHAR(50),@LEVEL5 INT,@Quantity5 as Float,@UOM5 as varchar(max),

  @Child6 VARCHAR(50),@Child_Name6 VARCHAR(max),@BOMType6 VARCHAR(50),@LEVEL6 INT,@Quantity6 as Float,@UOM6 as varchar(max),

  @Child7 VARCHAR(50),@Child_Name7 VARCHAR(max),@BOMType7 VARCHAR(50),@LEVEL7 INT,@Quantity7 as Float,@UOM7 as varchar(max),

  @Child8 VARCHAR(50),@Child_Name8 VARCHAR(max),@BOMType8 VARCHAR(50),@LEVEL8 INT,@Quantity8 as Float,@UOM8 as varchar(max),

  @Child9 VARCHAR(50),@Child_Name9 VARCHAR(max),@BOMType9 VARCHAR(50),@LEVEL9 INT,@Quantity9 as Float,@UOM9 as varchar(max),

  @Child10 VARCHAR(50),@Child_Name10 VARCHAR(max),@BOMType10 VARCHAR(50),@LEVEL10 INT,@Quantity10 as Float,@UOM10 as varchar(max)

   

   

  CREATE TABLE #BOMTable 

  (

  Father VARCHAR(max) ,

  FatherName VARCHAR(max),Child VARCHAR(max),ChildName VARCHAR(max),BomType VARCHAR(max),LEVELX int,Quantity float,UOM varchar(max)

  )

   

   

  DECLARE LEVEL0 CURSOR

   

   

  FOR SELECT T0.Code as 'Father',T2.ItemName as 'Father Name ',t1.Code as 'Child',T3.ItemName as 'Child Name',t3.TreeType as 'BOM Type',2 AS 'LEVEL',t1.Quantity,t3.InvntryUom

  FROM OITT T0 

  LEFT JOIN ITT1 T1 on T0.Code=T1.Father

  INNER join OITM T2 on T0.Code = T2.ItemCode

  INNER join OITM T3 on T1.Code = T3.ItemCode

  --WHERE T0.Code='NNFGLDBN00000001'

  ORDER by t1.VisOrder 

   

   

  OPEN LEVEL0

  FETCH NEXT FROM LEVEL0 INTO @Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM

  WHILE @@FETCH_STATUS = 0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM)

  ------------------Level 1-----------------------------

    IF @BOMType = 'P' 

  BEGIN

  DECLARE LEVEL1 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child order by L1.VisOrder 

  OPEN LEVEL1

  FETCH NEXT FROM LEVEL1 INTO @Child1,@Child_Name1,@BOMType1,@Quantity1,@UOM1

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child1,@Child_Name1,@BOMType1,3,@Quantity1,@UOM1)

  ------------------Level 2-----------------------------

  IF @BOMType1 = 'P' 

  BEGIN

  DECLARE LEVEL2 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child1 order by L1.VisOrder

  OPEN LEVEL2

  FETCH NEXT FROM LEVEL2 INTO @Child2,@Child_Name2,@BOMType2,@Quantity2,@UOM2

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child2,@Child_Name2,@BOMType2,4,@Quantity2,@UOM2)

   

   

  ------------------Level 3-----------------------------

  IF @BOMType2 = 'P' 

  BEGIN

  DECLARE LEVEL3 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child2 order by L1.VisOrder 

  OPEN LEVEL3

  FETCH NEXT FROM LEVEL3 INTO @Child3,@Child_Name3,@BOMType3,@Quantity3,@UOM3

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child3,@Child_Name3,@BOMType3,5,@Quantity3,@UOM3)

  ------------------Level 4-----------------------------

  IF @BOMType3 = 'P' 

  BEGIN

  DECLARE LEVEL4 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child3 order by L1.VisOrder

  OPEN LEVEL4

  FETCH NEXT FROM LEVEL4 INTO @Child4,@Child_Name4,@BOMType4,@Quantity4,@UOM4

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child4,@Child_Name4,@BOMType4,6,@Quantity4,@UOM4)

  ------------------Level 5-----------------------------

  IF @BOMType4 = 'P' 

  BEGIN

  DECLARE LEVEL5 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child4 order by L1.VisOrder

  OPEN LEVEL5

  FETCH NEXT FROM LEVEL5 INTO @Child5,@Child_Name5,@BOMType5,@Quantity5,@UOM5

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child5,@Child_Name5,@BOMType5,7,@Quantity5,@UOM5)

  ------------------Level 6-----------------------------

  IF @BOMType5 = 'P' 

  BEGIN

  DECLARE LEVEL6 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child5 order by L1.VisOrder

  OPEN LEVEL6

  FETCH NEXT FROM LEVEL6 INTO @Child6,@Child_Name6,@BOMType6,@Quantity6,@UOM6

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child6,@Child_Name6,@BOMType6,8,@Quantity6,@UOM6)

  ------------------Level 7-----------------------------

  IF @BOMType6 = 'P' 

  BEGIN

  DECLARE LEVEL7 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child6 order by L1.VisOrder

  OPEN LEVEL7

  FETCH NEXT FROM LEVEL7 INTO @Child7,@Child_Name7,@BOMType7,@Quantity7,@UOM7

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child7,@Child_Name7,@BOMType7,9,@Quantity7,@UOM7)

  ------------------Level 7-----------------------------

  IF @BOMType7 = 'P' 

  BEGIN

  DECLARE LEVEL8 CURSOR FOR SELECT L1.Code,L2.ItemName,L2.TreeType,L1.Quantity,L2.InvntryUom FROM ITT1 L1 LEFT JOIN OITM L2 on L1.Code=L2.ItemCode WHERE L1.Father=@Child7 order by L1.VisOrder

  OPEN LEVEL8

  FETCH NEXT FROM LEVEL8 INTO @Child8,@Child_Name8,@BOMType8,@Quantity8,@UOM8

  WHILE @@FETCH_STATUS=0

  BEGIN

  INSERT INTO #BOMTable values(@Father,@Father_Name,@Child8,@Child_Name8,@BOMType8,10,@Quantity8,@UOM8)

  FETCH NEXT FROM LEVEL8 INTO @Child8,@Child_Name8,@BOMType8,@Quantity8,@UOM8

  END

  CLOSE LEVEL8

  DEALLOCATE LEVEL8

  END

  ------------------Level 7-----------------------------

  FETCH NEXT FROM LEVEL7 INTO @Child7,@Child_Name7,@BOMType7,@Quantity7,@UOM7

  END

  CLOSE LEVEL7

  DEALLOCATE LEVEL7

  END

  ------------------Level 7-----------------------------

   

  FETCH NEXT FROM LEVEL6 INTO @Child6,@Child_Name6,@BOMType6,@Quantity6,@UOM6

  END

  CLOSE LEVEL6

  DEALLOCATE LEVEL6

  END

  ------------------Level 6-----------------------------

  FETCH NEXT FROM LEVEL5 INTO @Child5,@Child_Name5,@BOMType5,@Quantity5,@UOM5

  END

  CLOSE LEVEL5

  DEALLOCATE LEVEL5

  END

  ------------------Level 5-----------------------------

  FETCH NEXT FROM LEVEL4 INTO @Child4,@Child_Name4,@BOMType4,@Quantity4,@UOM4

  END

  CLOSE LEVEL4

  DEALLOCATE LEVEL4

  END

  ------------------Level 4-----------------------------

  FETCH NEXT FROM LEVEL3 INTO @Child3,@Child_Name3,@BOMType3,@Quantity3,@UOM3

  END

  CLOSE LEVEL3

  DEALLOCATE LEVEL3

  END

  ------------------Level 3-----------------------------

  FETCH NEXT FROM LEVEL2 INTO @Child2,@Child_Name2,@BOMType2,@Quantity2,@UOM2

  END

  CLOSE LEVEL2

  DEALLOCATE LEVEL2

  END

  ------------------Level 2-----------------------------

  FETCH NEXT FROM LEVEL1 INTO @Child1,@Child_Name1,@BOMType1,@Quantity1,@UOM1

  END

  CLOSE LEVEL1

  DEALLOCATE LEVEL1

  END

  ------------------Level 1-----------------------------

  FETCH NEXT FROM LEVEL0 INTO  @Father,@Father_Name,@Child,@Child_Name,@BOMType,@LEVEL,@Quantity,@UOM

  END

  SELECT * FROM #BOMTable T0 order by T0.Father

  DROP TABLE #BOMTable

  CLOSE LEVEL0

  DEALLOCATE LEVEL0

  This query can make more than level 20 right

  0

Please sign in to leave a comment.