Stückliste per sql rekursiv ermitteln

Markus Rewak

Hallo,

kennt sich jemand von euch mit rekursiven Abfragen aus?
Ich benötige folgende SQL-query:

Als Parameter möchte ich gerne den Vaterartikel und 1 Komponentenartkel eingeben. Daraufhin soll für diesen bestimmten Komponentenartikel die Summe der Menge über die komplette Stücklistenstruktur ermittelt werden . Die Stückliste kann n Stufen haben.

Ich habe folgendes über Rekursion herausgefunden, jedoch ist das noch nicht was ich will:

WITH Erreichbar(Von, Nach) AS (
SELECT father AS Von, code AS Nach
FROM itt1
UNION ALL
SELECT A.Von, B.code AS Nach
FROM Erreichbar A, itt1 B
WHERE A.Nach = B.father )

SELECT Von, Nach
FROM Erreichbar
WHERE von ='0200430000'


Weiss jemand die Lösung?

Gruß Markus

Martin Cerasuolo

Can you ask your question in English, than maybe i can help you?

Best regards,


Computerplan(The Netherlands)


Martin Cerasuolo

Markus Rewak

hi Martin,

i'm looking for a sql statement to summarize the quantity of a component in the product tree .

For example:
if the following is my product tree

A0
...A007 2 pieces
...A1 2 pieces
.......A11 2pieces
.......A12 5pieces
.......A2 10pieces
.............A21 3 pieces
.............A11 2 pieces
.............A3 2 pieces
...................A11 5pieces
...................A9 2 pieces
...

input parameters: the first father item( A1), the component (A11)
expected result: A11 --> 2 + 10*2 + 2*5 = 32 pieces

is such an sql statement possible?

best regards

Markus

Martin Cerasuolo

Hi,

Is this query based on the software from SAP Business One version 2005 Service Pack 01 Patch Level 20?

Martin

Martin Cerasuolo

Sorry i saw that you use the table itt1, so i gues it is. Give me 10 minutes and i will create it for you.

Markus Rewak

hi Martin,

i'm looking for a sql statement to summarize the quantity of a component in the product tree .

For example:
if the following is my product tree

A0
...A007 2 pieces
...A1 2 pieces
.......A11 2pieces
.......A12 5pieces
.......A2 10pieces
.............A21 3 pieces
.............A11 2 pieces
.............A3 2 pieces
...................A11 5pieces
...................A9 2 pieces
...

input parameters: the first father item( A1), the component (A11)
expected result: A11 --> 2 + 10*2 + 2*5 = 32 pieces

is such an sql statement possible?

best regards

Markus

Markus Rewak

ups, double posted.

yes, i'm using SAP Business One 2005A SP01 PL29

Martin Cerasuolo

Yes,

The following query must be run in SAP Business One. Then a father item and a component item must be selected.

T1.Qauntity = Father quantity
T0.Quantity = Component quantity

SELECT (T1.Qauntity + (T1.Qauntity*T0.Quantity)) as Value, T1.Code,T1.Qauntity, T0.Code, T0.Quantity
FROM ITT1 T0
INNER JOIN OITT T1 ON T0.Father = T1.Code
where T0.Code='[%Code]'

A simple calculation is used to show you how it can work. If you use a bill of material in a bill of material than this query doesn't work.

Can you give me an explanation of the figures in your result?

Best regards,

Martin Cerasuolo

Martin Cerasuolo

SELECT (T1.Qauntity + (T1.Qauntity*T0.Quantity)) as Value, T1.Code,T1.Qauntity, T0.Code, T0.Quantity
FROM ITT1 T0
INNER JOIN OITT T1 ON T0.Father = T1.Code
where T0.Father='[%Father]' and T0.Code='[%Code]'

The last piece of the query was missing. So hereby...

Philipp Knecht

Hi Markus, hi Martin

There is a way to make rekurisve queries.
With SQL 2005 there is a new method called CTE:

http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm#Figure5

and

http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp

explains it well.

hth

Andreas Schlingmann

Ist zwar schon etwas her, aber da ich die Lösung gerade parat habe, hier ein Beispiel :

WITH rek (father, code, quantity) AS 
(
SELECT r.father, r.code, r.quantity
FROM itt1 r
WHERE r.father = 'KOPFARTIKEL'
UNION ALL
SELECT kind.father, kind.code, kind.quantity
FROM rek vater,
itt1 kind
WHERE vater.code = kind.father
)

SELECT DISTINCT father, a.itemname, code, b.itemname, quantity
FROM rek
inner join oitm a on father=a.itemcode
inner join oitm b on code=b.itemcode
ORDER BY father, code, quantity

Philipp : Warum macht ihr eure Stücklistenauflösung nicht auch so ? Derzeit geschieht das glaube ich iterativ (max 9 stufig).

Gruß
Andreas

Philipp Knecht

Hi Andreas

Danke für diese Lösung.
Wir können im Moment noch keine CTE machen weil diese vom SQL Server 2000 nicht unterstützt werden.

hth

Torsten Schacht

[quote]"UnirezSCH"]
Ist zwar schon etwas her, aber da ich die Lösung gerade parat habe, hier ein Beispiel :.............
[/quote]

Moin Andreas,
danach habe ich gerade gesucht und konnte Deinen Vorschlag auch gleich auf unsere Datenbank anwenden.
Nun möchte ich aber eine Stückliste mehrstufig in Struktur-Form ausgeben.
Die Daten sind komplett nur komme mit dem Sortieren nicht weiter.
Hier ein Beispiel:

CREATE TABLE #TEILLIST
(tl_idnr int, tl_vidnr int, tl_sidnr int, tl_posnr int );
INSERT INTO #TEILLIST VALUES (1, 231084, 199608,10);
INSERT INTO #TEILLIST VALUES (2, 228563, 197819,10);
INSERT INTO #TEILLIST VALUES (3, 231084, 196086,30);
INSERT INTO #TEILLIST VALUES (4, 231084, 173855,40);
INSERT INTO #TEILLIST VALUES (5, 231084, 228563,20);
INSERT INTO #TEILLIST VALUES (6, 228563, 242846,20);

CREATE TABLE #NFSTAMM
(id int, nft_idnr int, nft_sth0 char(29));
INSERT INTO #NFSTAMM VALUES (1, 231084, 'Kopfteil');
INSERT INTO #NFSTAMM VALUES (2, 199608, 'Teil 1');
INSERT INTO #NFSTAMM VALUES (3, 197819, 'Teil 3');
INSERT INTO #NFSTAMM VALUES (4, 196086, 'Teil 5');
INSERT INTO #NFSTAMM VALUES (5, 173855, 'Teil 6');
INSERT INTO #NFSTAMM VALUES (6, 228563, 'Teil 2');
INSERT INTO #NFSTAMM VALUES (7, 242846, 'Teil 4');

WITH rek (tl_idnr,tl_vidnr, tl_sidnr, tl_posnr,[level]) AS
(
SELECT r.tl_idnr,r.tl_vidnr, r.tl_sidnr, r.tl_posnr,1
FROM #teillist r
WHERE r.tl_vidnr = 231084
UNION ALL
SELECT kind.tl_idnr,kind.tl_vidnr, kind.tl_sidnr, kind.tl_posnr, [level]+1
FROM rek vater,
#teillist kind
WHERE vater.tl_sidnr = kind.tl_vidnr
)
SELECT tl_idnr, tl_vidnr, a.nft_sth0, tl_sidnr, b.nft_sth0, tl_posnr, [level]
FROM rek
INNER JOIN #nfstamm a ON tl_vidnr = a.nft_idnr
INNER JOIN #nfstamm b ON tl_sidnr = b.nft_idnr

--so soll es aussehen
--ORDER BY b.nft_sth0

-- DROP TABLE #TEILLIST
-- DROP TABLE #NFSTAMM


Herzliche Grüße aus Friesland
Torsten

klaus ebert

Hi Andreas,
ist es möglich in der Rekursiven Abfrage eine Variable [%0] einzubauen??
Lassen sich die gelben Pfeile irgendwie einblenden?

nette Grüße
Klaus

Manuel Marhold

Hi Torsten,

klappt das mit der Sortierung so nicht?

@klaus: du meinst wahrscheinlich, diese Abfrage im SAP Abfrage-Manager ausführen.
Variable sollte gehen, dann musst du die Tabellen aber mit T0, T1 etc. bennenen.
der orangene Pfeil geht nur, wenn SAP Tabelle und Feld zuweisen kann, das wird also wohl nicht gehen.
Du kannst evtl. die Abfrage von Andreas mit der OITT joinen und den Code aus der OITT ausgeben, dann geht das evtl.

klaus ebert

das Problem mit dem Sortieren hab ich leider auch noch.
es soll nach der Stufe der Stückliste sortiert werden.
hat jemand eine Idee??

klaus ebert

das Problem mit dem Sortieren hab ich leider auch noch.
es soll nach der Stufe der Stückliste sortiert werden.
hat jemand eine Idee??

Manuel Marhold

Hi Klaus,

was für eine Fehlermeldung bekommst du denn, wenn du das Order by aktivierst?

Torsten Schacht

[quote]"ManuelMarhold"]
Hi Torsten,
klappt das mit der Sortierung so nicht?
[/quote]
Moin Manuel,
ORDER BY b.nft_sth0
würde funktionieren .... nur im richtigen Leben würden die Teile ja anders heissen.
z.B. Schraube, Blech, Träger....
und dann könnte man danach nicht mehr sortieren
Ich habe sie nur so genannt, damit ich die gewünscht Ausgabe simulieren kann
Gruß Torsten

Manuel Marhold

Hi,

warum sortierst du dann nicht nach der ID?

Torsten Schacht

[quote]"ManuelMarhold"]
Hi,
warum sortierst du dann nicht nach der ID?
[/quote]
Moin,
weil dies ebenfalls nicht das gewünschte Ergebnis liefert.
So sollte es aussehen:
1 231084 Kopfteil 199608 Teil 1101
5 231084 Kopfteil 228563 Teil 2201
2 228563 Teil 2 197819 Teil 3102
6 228563 Teil 2 242846 Teil 4202
3 231084 Kopfteil 196086 Teil 5301
4 231084 Kopfteil 173855Teil 6401

Gruß Torsten

Philipp Knecht

Hallo Freunde der Rekursion
Dank euren vielfältigen Beiträgen hier sind wir einen Riesenschritt weiter gekommen.
habe es ebenfalls mit diesen CTE's versucht dann gehen aber gewisse Informationen verloren und die Sortierung stimmte auch nicht.
Fakt ist dass uns eine RowID fehlt um da gescheit was zu machen.
Ein Select * bringt die richtige Reihenfolge (B1 scheint dass immer richtig so komplett zu speichern?)
Deshalb ist die bestehende Variante im Standard die einzige welche die Liste auch so darstellt wie sie in b1 gespeichert wurde.
Die Einschränkungen Levels und Anz. Pos pro Level waren aber unschön deshalb hier die Lösung:

          SELECT Code,Qauntity AS Qty,Qauntity AS MainQty,REPLICATE('0',100) AS SortExpr,0 AS Lvl, 'Y' AS IsTreeCode  INTO #Lvl00 FROM OITT WHERE Code = '[%TreeCode]'
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,10) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',80)  AS SortExpr,1 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl01 FROM ITT1 T0 INNER JOIN #Lvl00 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,20) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',70)  AS SortExpr,2 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl02 FROM ITT1 T0 INNER JOIN #Lvl01 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,30) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',60)  AS SortExpr,3 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl03 FROM ITT1 T0 INNER JOIN #Lvl02 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,40) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',50)  AS SortExpr,4 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl04 FROM ITT1 T0 INNER JOIN #Lvl03 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,50) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',40)  AS SortExpr,5 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl05 FROM ITT1 T0 INNER JOIN #Lvl04 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,60) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',30)  AS SortExpr,6 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl06 FROM ITT1 T0 INNER JOIN #Lvl05 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,70) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',20)  AS SortExpr,7 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl07 FROM ITT1 T0 INNER JOIN #Lvl06 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,80) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR) + REPLICATE('0',10)  AS SortExpr,8 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl08 FROM ITT1 T0 INNER JOIN #Lvl07 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT T0.Code,T0.Quantity AS Qty,CASE WHEN T2.Code IS NOT NULL THEN T1.MainQty * T0.Quantity ELSE T1.MainQty END AS MainQty,LEFT(T1.SortExpr,90) + REPLICATE('0',10 - LEN(T0.ChildNum + 1)) + CAST(T0.ChildNum + 1 AS NVARCHAR)                      AS SortExpr,9 AS Lvl, CASE WHEN T2.Code IS NULL THEN 'N' ELSE 'Y' END AS IsTreeCode  INTO #Lvl09 FROM ITT1 T0 INNER JOIN #Lvl08 T1 ON T0.Father = T1.Code LEFT OUTER JOIN OITT T2 ON T0.Code = T2.Code
SELECT * INTO #Result FROM #Lvl00
UNION ALL SELECT * FROM #Lvl01
UNION ALL SELECT * FROM #Lvl02
UNION ALL SELECT * FROM #Lvl03
UNION ALL SELECT * FROM #Lvl04
UNION ALL SELECT * FROM #Lvl05
UNION ALL SELECT * FROM #Lvl06
UNION ALL SELECT * FROM #Lvl07
UNION ALL SELECT * FROM #Lvl08
UNION ALL SELECT * FROM #Lvl08
SELECT T0.*, T1.* FROM #Result T0 INNER JOIN OITM T1 ON T0.Code = T1.ItemCode ORDER BY SortExpr

Kann man auf mehr als 10 Levels aufbohren wenn man möchte und zeigt pro Ebene max 10 stellige Komponentenanzahl dar (dürfte reichen;) )
hth

Manuel Marhold

Hi.

Hier ist noch ein Fehler drin:

Wenn ich einen Stückliste B in einer Stückliste A mit der Menge 2 packen, und in der Stückliste B eine Position 4711 mit der Menge 5 enthalten ist, muss er als Menge 10 ausgeben, nicht 5.

Philipp Knecht

Hi manuel

Danke für die Feststellung.
Wir geben ja immer die MainQty (Die Stücklistenmenge) und die Komponentenmenge mit.
Ich denke das Problem ist somit im Layout lösbar.

Wir haben halt im Standard noch nicht allzuviel berechnet weil es auch hier verschieden Sichtweisen gibt.

hth

Manuel Marhold

Hi Philipp,

okay, wenn man es so sieht: O.K. ;)

Merlin

Hi Zusammen, 

 

hier noch ein Vorschlag incl. Sortierung und aufgerechneten Mengen. Wer mit Stücklisten mit mehr als 998 Zeilen arbeitet einfach den Multiplikator im rekursiven Abschnitt erhöhen.

 

Viele Grüße

Reimer Bulling