Apr 16, 2019, updated at Apr 18, 2019 (UTC)

3852

Stücklisten Auflistung MSSQL

Hallo!

Ich habe folgendes Problem:

Ich habe im Artikelstamm (Part$Product_2_1) die Artikel des ERP Systems.
Zu jedem Produktionsartikel gibt es eine Fertigungsliste (Part$Product_2_1_TAB) die aus Arbeitschritten (Operation$Operation_7_0) und wiederum aus Zukaufsteilen und weitern Fertigungsteilen Baugruppen) besteht. Die Baugruppen können wieder Unterbaugruppe und Zukaufsteile enthalten.
Die Fertigungsliste Part$Product_2_1_TAB ist mit dem Hauptartikel Part$Product_2_1 verknüpft und die Positionen der Fertigungliste wieder mit Part$Product_2_1 damit ich die Namen der Artikel der Positionen sehe.

Dies geht je nach Artikel über mehrere Stufen.

Meine momentane Lösung sieht so aus das ich über sechs joins die Teile zum Hauptartikel sehe. Jedoch immer in eigenen Spalten.
Mein Wunsch (Ziel) wäre den Hauptartikel und ALLE zugehörigen Artikel in einer Spalte aufzulisten.

Im Anhang Bild sieht man das mein Hauptartikel der 2.8705.051.5 ist dieser hat dann untern Anderem wieder die Baugruppe 2.8705.051.3 (Position1) der hätte unter Anderem wieder die Baugruppe 2.8705.053.3 (Postion 2) und darunter geht es immer so weiter. Postion 5 wäre in dem Fall NULL weil es nicht weiter nach "unten" geht.

Ich hätte eben gerne den Hauptartikel ausgewählt und dann darunter ALLE zugehörigen Teile angezeigt werden auch die der Baugruppen.

Hat jemand eine Idee wie ich das umsetzen könnte. Bei letzten Problem haben mir ukulele und Madmax sehr geholfen!

Ich hoffe ich konnte es halbwegs beschreiben.

Meine bisherige Abfrage:

select 

ART.such AS Hauptartikel,
CASE WHEN ARTP.nummer <> '' THEN ARTP.nummer WHEN ARG.nummer <> '' THEN ARG.nummer END AS Position1_Id,  
CASE WHEN ARTP.such <> '' THEN ARTP.such WHEN ARG.such <> '' THEN ARG.such END AS Position1,  
CASE WHEN ARTP.such <> '' THEN ARTP.bsart  END AS Position1_Bsart,  
CASE WHEN ARTP1.such <> '' THEN ARTP1.such WHEN ARG1.such <> '' THEN ARG1.such END AS Position2,  
CASE WHEN ARTP2.such <> '' THEN ARTP2.such WHEN ARG2.such <> '' THEN ARG2.such END AS Position3,  
CASE WHEN ARTP3.such <> '' THEN ARTP3.such WHEN ARG3.such <> '' THEN ARG3.such END AS Position4,  
CASE WHEN ARTP4.such <> '' THEN ARTP4.such WHEN ARG4.such <> '' THEN ARG4.such END AS Position5,  
CASE WHEN ARTP5.such <> '' THEN ARTP5.such WHEN ARG5.such <> '' THEN ARG5.such END AS Position6,  
POS.PK_zid,
POS.FK_elex,
CAST(ART.PK_id AS CHAR(30))PK_id

from dbo.Part$Product_2_1 ART (NOLOCK)
left join Part$Product_2_1_TAB POS ON POS.FK_id = ART.PK_id
left join Part$Product_2_1 ARTP ON ARTP.PK_id = POS.FK_elex            (elex ist die PositionsId der Fertigungsliste)
left join Operation$Operation_7_0 ARG ON ARG.PK_id = POS.FK_elex   (Hier wird auf Arbeitsschritte gejoint)

left join Part$Product_2_1_TAB POS2 ON POS2.FK_id = ARTP.PK_id
left join Part$Product_2_1 ARTP1 ON ARTP1.PK_id = POS2.FK_elex
left join Operation$Operation_7_0 ARG1 ON ARG1.PK_id = POS2.FK_elex

left join Part$Product_2_1_TAB POS3 ON POS3.FK_id = ARTP1.PK_id
left join Part$Product_2_1 ARTP2 ON ARTP2.PK_id = POS3.FK_elex
left join Operation$Operation_7_0 ARG2 ON ARG2.PK_id = POS3.FK_elex

left join Part$Product_2_1_TAB POS4 ON POS4.FK_id = ARTP2.PK_id
left join Part$Product_2_1 ARTP3 ON ARTP3.PK_id = POS4.FK_elex
left join Operation$Operation_7_0 ARG3 ON ARG3.PK_id = POS4.FK_elex

left join Part$Product_2_1_TAB POS5 ON POS5.FK_id = ARTP3.PK_id
left join Part$Product_2_1 ARTP4 ON ARTP4.PK_id = POS5.FK_elex
left join Operation$Operation_7_0 ARG4 ON ARG4.PK_id = POS5.FK_elex

left join Part$Product_2_1_TAB POS6 ON POS6.FK_id = ARTP4.PK_id
left join Part$Product_2_1 ARTP5 ON ARTP5.PK_id = POS6.FK_elex
left join Operation$Operation_7_0 ARG5 ON ARG5.PK_id = POS6.FK_elex

GROUP BY ART.such,ART.name,ARTP.such,ARTP1.such,ARTP2.such,POS.PK_zid,POS.FK_elex,ART.PK_id,ARG.such,ARG1.such,ARG2.such,ARTP3.such,ARG3.such,ARTP4.such,ARG4.such,ARTP5.such,ARG5.such,ARTP.nummer,ARG.nummer,ARTP.bsart
order by POS.PK_zid;

Please also mark the comments that contributed to the solution of the article

Content-Key: 441153

Url: https://administrator.de/contentid/441153

Printed on: April 19, 2024 at 02:04 o'clock

21 Comments

Latest comment

Hallo zusammen,

hier wäre ein Ansatz:
Transpose Table

Ergänzend dazu würde ich das gesamte Select in eine Common Table Expression packen, dann werden die Felder übersichtlicher dargestellt.

Ich denke auch mit row_number könnte man eine Lösung bauen.

Bitte kein PIVOT, da wird einem ja schumrig bei. Der richtige Weg kann nur sein CTE zu nutzen und dazu gibt es schon jede Menge Treffer allein zu Stücklisten:
https://social.msdn.microsoft.com/Forums/security/de-DE/4b1c36be-a832-46 ...
https://stackoverflow.com/questions/239275/how-do-i-create-a-recursive-q ...
https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hier ...

Und wenn man da wirklich tief einsteigen will gibt es bei MSSQL sogar einen eigenen Datentyp HierachrchyID und eine Suche danach findet sehr ausführliche Beschreibungen zum Thema, angefangen bei der klassischen Stückliste.
https://www.sqlshack.com/use-hierarchyid-sql-server/
Einen anderen sehr schönen Artikel finde ich leider nicht, aber das Prinzip ist gut. Häufig kommt natürlich die DB nicht von dir sondern von einer Anwendung, dann bleibt vermutlich nur CTE das adequat zu lösen.

Danke für die Links!
Obwohl ich momentan sagen muss ich hab keine Schimmer wie ich das mit meinen Daten umsetzen kann.

Ich werd mal "herumprobieren".

Danke!

Hi ukulele!

Ich checks einfach nicht. Kannst du mir anhand meines Codes etwas helfen wie ich die Abfrage aufbauen müsste?

Das wär echt super. Allein komm ich zu keiner Lösung.

Liebe Grüße

Hier mal mein Versuch deine Struktur nachzubauen:

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	UNION ALL
	SELECT	t2.Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t2.pfad + '\' + t1.ART_PK_id AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2

Das sind jetzt nur einige Spalten, da fehlt noch was aber das kann man alles ausschmücken. t1 bilde ich nur weil dort viele Joins anfallen, das könnte man eventuell auch weg lassen. Wenn man das aber direkt in t2 integriert gehen glaube ich gar keine LEFT JOINs, daher ist das übersichtlicher und eventuell auch der einzige Weg. Oder man joint die anderen Spalten, z.B. aus Operation$Operation_7_0, erst ganz am Ende.

In t2 wirds dann rekursiv. Alle Teile, die Bestandteil von der Position in t1 sind, werden angehängt. Es entsteht eine Liste aller Teile die in der Hauptposition sind oder in einem Teil der Hauptposition.

Das kann dann im äußeren Select z.B. aggregiert werden (da wird es vermutlich noch irgendwo eine Anzahl geben und eventuell sind Bestandteile identisch und werden mehrmals gebraucht.

Man kann das ganze sehr gut mit einem Pfad veranschaulichen und/oder eine Spalte wie die erste Artikelnummer durchschleifen (siehe Spalte Hauptartikel).

Das ist die klasische Stückliste, das was dein Bild zeigt wäre tatsächlich eher PIVOT. Aber die releationale DB arbeitet eher mit Zeilen für gleichartige Dinge und ist darauf ausgelegt. Damit ist es dann auch einfacher weiter zu rechnen etc.

Viiieeeeleeee Dank!

Ich werds am Dienstag gleich testen!

Wünsche frohe Ostern!

Hallo!

Vielen Dank für die Mühe!!!
Folgendes Problem ich sehe ganz kurz Ergebnisse und dann kommt folgende Meldung:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

Weiß du was da das Problem ist?

lg Roland

Nunja Wissen ist relativ, eigentlich steht es da ja. Guck mal bitte durch wo Spalten eines Zeichenkettentyps mit numerischen Werten verglichen oder zusammengefügt werden.

Welche Datentypen haben POS_FK_elex , ART_PK_id und ART_such ?

Hallo Danke!

Kann ich den Datentypen irgendwie "auslesen"
POS_FK_elex sowie ART_PK_id müssten rein nummerische Werten sein. ART_such kann auch alphanumerisch sein.

lg

Am besten testest du erstmal jeden Select nacheinander. Also
1)

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	)
SELECT	*
FROM	t1

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	)
SELECT	*
FROM	t2

und dann Alles. Ich vermute das Problem tiritt erst mit dem gesammten Code auf, warscheinlich liegt es an der Spalte pfad. Probier mal

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	UNION ALL
	SELECT	t2.Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t2.pfad + '\' + cast(t1.ART_PK_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2

Du bist echt der König!

Mit der letzten Abfrage hats geklappt und wie es scheint hab ich sogar alles drin was ich brauche!!!

Vielen Dank!

Wenn ich noch eine Frage stellen darf.
Die Abfrage dauert natürlich wenn ich sie über viele oder gar alle Artikel laufen lasse sehr lange.

Mit welcher Methode kann ich das Nachts machen lassen? Geht das mit einem View den ich zu einer gewissen Zeit starte oder ist das der falsche Ansatz?

lg Roland

Eine View wird immer ausgeführt wenn sie aufgerufen wird. Ich bin nicht sicher wie das genau bei einer materialized view läuft, ich glaube aber das es der falsche Ansatz ist. Die Fragen sind doch erstmal was genau dauert lange (lass dir mal die Ausführungspläne im SQL Studio mit anzeigen beim ausführen), kann man die Ausführung beschleunigen (z.B. mit einem Index auf POS.FK_id), macht es überhaupt Sinn das für alle Daten zu tun (WHERE-Filter eventuell auch schon in den ersten Select packen) und zu guter letzt ließe sich das natürlich in eine Art Auswertungstabelle des Nachts ablegen. Es kommt darauf an wofür die Daten gebraucht werden.

Ok, Danke! Später sollen diese Daten über Qlikview analysiert werden und da wirds dann einen eigenen Server geben wo man die Reports in der Nacht generieren kann. Also ist es nicht so tragisch.

Jetzt muss ich dich aber noch mit einer Sache quälen dann halt ich die Füße still.

In dem Anhang sieht man das der Artikel (Baugruppe) 2.2421.003.1-A noch aus dem LF-2.2421.003.1 dem dazugehörigen HF und dem ALU-ST-DM60 besteht.

Siehst du eine Möglichkeit das ich eine Spalte mit einem Kennzeichen setze das die zusammengehören? Oder würde das über den Pfad gehen?

Im Prinzip hab ich deinen nicht viel verändert weil er das liefert was ich wollte trotzdem schicke ich ihn mit

WITH t1 AS (
	SELECT	
			CAST(ART.PK_id AS CHAR(30))ART_PK_id,
			ART.such AS ART_such,
			ART.bsart AS ART1_bsart,
			POS.FK_elex AS POS_FK_elex,
			ART.MANDANT_ID AS ART_MANDANT_ID,
			CASE WHEN ART1.such <> '' THEN ART1.such WHEN ARG.such <> '' THEN ARG.such END AS Position  
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	LEFT JOIN dbo.Part$Product_2_1 ART1 ON POS.FK_elex = ART1.PK_id
	), t2 AS (
	SELECT	
			t1.ART_PK_id AS Hauptartikel,
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such as baugruppe,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_MANDANT_ID = 3 and t1.ART_such ='2.2421.001.5'  
	UNION ALL
	SELECT	t2.Hauptartikel,
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t2.pfad + '\' + cast(t1.ART_PK_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2;

Ich würde behaupten alles ist machbar aber sieht man denn nicht an der Spalte "Hauptartikel" das die zusammen gehören? Du kannst natürlich jede beliebige Spalte durchschleifen oder mit CASE etc. deinen Bedürfnissen anpassen. Wenn jetzt z.B. Ebene 2 immer die Hauptbaugruppe darstellt und das weiter vererben soll (ähnlich wie Ebene 1 im Prinzip den Hauptartikel weiter vererbt), dann würde ich folgendes tun:

WITH t1 AS (
	SELECT	
			CAST(ART.PK_id AS CHAR(30))ART_PK_id,
			ART.such AS ART_such,
			ART.bsart AS ART1_bsart,
			POS.FK_elex AS POS_FK_elex,
			ART.MANDANT_ID AS ART_MANDANT_ID,
			CASE WHEN ART1.such <> '' THEN ART1.such WHEN ARG.such <> '' THEN ARG.such END AS Position  
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	LEFT JOIN dbo.Part$Product_2_1 ART1 ON POS.FK_elex = ART1.PK_id
	), t2 AS (
	SELECT
			1 AS Ebene,
			t1.ART_PK_id AS Hauptartikel,
			CAST(NULL AS CHAR(30)) AS Hauptbaugruppe,
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such AS baugruppe,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_MANDANT_ID = 3 and t1.ART_such ='2.2421.001.5'  
	UNION ALL
	SELECT	t2.Ebene + 1,
			t2.Hauptartikel,
			(CASE WHEN t2.Ebene + 1 = 2 THEN CAST(t1.ART_PK_id AS CHAR(30)) ELSE t2.Hauptbaugruppe END),
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t2.pfad + '\' + cast(t1.ART_PK_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2;

Hallo!
Danke für die Antwort!

Ja die gehören zum Hauptartikel, bilden aber eine eigenen "Unterbaugruppe". Zum Hauptartikel gehören auch noch weitere "Unterbaugruppen". Ich hab den Snapshot etwas unglücklich gewählt!

Ich werd morgen mal testen.

Wünsche noch einen schönen Abend!

Hallo nochmal!
In Zeile 32 muss ich statt Hauptgruppe aber Hauptartikel reinschreiben oder? Wenn ja dann meckert er folgendes:

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Alles zurück, kann sein das ich einen Blödsinn gemacht hab. Schaus mir morgen an. Ich arbeite von zuhaus über Teamviewer da ist es etwas "unübersichtlich"!

Ich hab das nicht getestet, nichtmal die Syntax^^

Aber die Anzahl der Spalten stimmt, da muss dir was verrutscht sein.

Ja, alles SUPER! War mein Fehler!

Könnte man diesen "Durchlauf" mit der Ebene eigentlich noch um eine Ebene mehr erweitern? Weil eine "Unterbaugruppe" kann wieder eine Unterbaugruppe haben?

lg Roland

Du könntest technisch ca. 32768 Rekursionen in einem "Durchgang" machen, dannach wirds tricky.

Wieviele Baugruppen Unterbaugruppen sind ist der DB dabei erstmal egal. Was eine Hauptbaugruppe und was eine Unterbaugruppe ist und was dann in welcher Spalte weiter vererbt wird (wie im Beispiel Hauptartikel und Hauptbaugruppe) das ist eine Gestaltungsfrage. Du kannst das im CASE auch von anderen Werten abhängig machen, z.B. mit

(CASE WHEN t1.ART1_bsart IN ( 'Hauptartikel,'Hauptbaugruppe','Unterbaugruppe' ) THEN CAST(t1.ART_PK_id AS CHAR(30)) ELSE t2.Hauptbaugruppe END),  

German solved Question Databases Development

Hotly discussed

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments

WIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 1 Comment

End of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment