Komplexe rekursive Abfrage

Entwicklung
1

Hallo zusammen,
ich stehe vor einer größeren Aufgabe und hoffe, dass ihr mir hier vielleicht helfen könnt…

Ich habe folgende Tabelle:

BELEGNUMMER, QUELLBELEG
R014931 L013119
L013119 X010899
X010899 X009401
X009401 null

Jetzt möchte ich gerne nach R014931 suchen und folgendes Ergebnis haben:

Rechnung Lieferschein Auftrag Auftrag2 Angebot
R014931 L013119 X010899 X009401 null

In den Nummern steckt quasi schon der Belegtyp

Rechnung =R
Angebot=A
Lieferscheine=L
Aufträge=X
Bestellungen=B
Gutschriften=G

Das ganze soll so aufgebaut sein, dass wenn man nach einer Guschrift sucht, alles ausgegeben wird, was sich hinter den Quellbelegen verbirgt.

Ich freue mich über Ansätze.

Viele Grüße

René Heimberger

2

Sind es nie mehr als 2 Aufträge?

Logik in Werte zu codieren (=erstes Zeichen definiert den Typ) ist übrigens keine gute Idee auf lange Sicht.

3

Es können unter Umständen von allem mehrere Werte vorhanden sein. Das macht es schwierig. Da ich die Abfrage in einer Software nutze, brauche ich eine finale anwendbare view, auf die ich zugreifen kann. Ein Kompromiss wäre

Gutschrift Rechung Lieferschein Auftrag Bestellung Angebot

Dann würde der erste Treffer eben in die entsprechende Spalte geschrieben werden müssen.

4

Du kannst keine SQL Abfrage schreiben, die bei jedem Aufruf eine andere Anzahl von Spalten zurückgibt.

Man könnte alle Auftragsnummern in einer Spalte als Array oder Komma separierte Liste zurückgeben.

5

Wie gesagt, ich würde jeweils den ersten Treffer nehmen, weil in der Regel nur ein Beleg vorhanden ist.

Dynamisch müsste es halt anhand der Suchbelegs sein. Wenn ich nach einem Lieferschein suche, kommt natürlich keine Gutschrift, Rechnung zurück. Dafür müsste man in die andere Richtung suchen und man würde NULL zurückbekommen.

6

Hier eine Umsetzungsmöglichkeit. Es wird immer nur der 1. Beleg jeder Belegart verwendet.
Aber auch ich bin der Meinung das es nicht gut ist, wenn man Daten einfach weg lässt.

create table beleg
(
    belegnummer varchar(25)
    , quellbeleg varchar(25)
);

insert into beleg
    values
        ('R014931', 'L013119')
        ,('L013119', 'X010899')
        ,('X010899', 'X009401')
        ,('X009401', null);

select * from beleg;

 belegnummer | quellbeleg 
-------------+------------
 R014931     | L013119
 L013119     | X010899
 X010899     | X009401
 X009401     | 
(4 Zeilen)

\set beleg 'R014931'

with recursive beleg_query as (
    select b.*
        from beleg b
        where belegnummer = :'beleg'
    union
    select b.*
        from beleg b
            join beleg_query q
                on b.quellbeleg = q.belegnummer or b.belegnummer = q.quellbeleg
)
, belege_found as (
	select 	q.*
			, substr(belegnummer, 1, 1) as belegart
			, row_number() over (partition by substr(belegnummer, 1, 1)) as beleg_sort
	    from beleg_query q
)
select 
	(select belegnummer from belege_found where belegart = 'A' and beleg_sort = 1) as angebot,
	(select belegnummer from belege_found where belegart = 'X' and beleg_sort = 1) as auftrag,
	(select belegnummer from belege_found where belegart = 'B' and beleg_sort = 1) as bestellung,
	(select belegnummer from belege_found where belegart = 'L' and beleg_sort = 1) as lieferschein,
	(select belegnummer from belege_found where belegart = 'R' and beleg_sort = 1) as rechnung,
	(select belegnummer from belege_found where belegart = 'G' and beleg_sort = 1) as gutschrift
;

 angebot | auftrag | bestellung | lieferschein | rechnung | gutschrift 
---------+---------+------------+--------------+----------+------------
         | X010899 |            | L013119      | R014931  | 
(1 Zeile)
7

Das ist ja Mega!
Ich hab auch nochmal nachgedacht und finde den Ansatz, mehrfachtreffer mit einem Komma getrennt entsprechend zu erfassen, für gut.
Wie müsste man dafür das ganze abändern? Ich muss zugeben, dass ich hier SQL-Technisch an meine Grenzen gerate.

8 
with recursive beleg_query as (
    select b.*
        from beleg b
        where belegnummer = :'beleg'
    union
    select b.*
        from beleg b
            join beleg_query q
                on b.quellbeleg = q.belegnummer or b.belegnummer = q.quellbeleg
)
select 
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'A') as angebot,
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'X') as auftrag,
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'B') as bestellung,
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'L') as lieferschein,
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'R') as rechnung,
	(select string_agg(belegnummer, ', ') from beleg_query where substr(belegnummer, 1, 1) = 'G') as gutschrift
;

 angebot |     auftrag      | bestellung | lieferschein | rechnung | gutschrift 
---------+------------------+------------+--------------+----------+------------
         | X010899, X009401 |            | L013119      | R014931  | 
(1 Zeile)
9

Um die gewünschten Ergebnisse zu erzielen, können wir einen Algorithmus implementieren, der die gegebene Tabelle analysiert und die erforderlichen Informationen extrahiert. Hier ist ein möglicher Ansatz in Python:

def belegsuche(tabelle, belegnummer):
belegtypen = {‘R’: ‘Rechnung’, ‘A’: ‘Angebot’, ‘L’: ‘Lieferschein’, ‘X’: ‘Auftrag’, ‘B’: ‘Bestellung’, ‘G’: ‘Gutschrift’}
ergebnis = [belegnummer] # Initialisiere das Ergebnis mit der gesuchten Belegnummer

while belegnummer in tabelle:
    quellbeleg = tabelle[belegnummer]
    if quellbeleg is None:
        break
    belegtyp = belegtypen[belegnummer[0]]
    ergebnis.append(quellbeleg)
    belegnummer = quellbeleg

belegtypen_ergebnis = [belegtypen[beleg[0]] if beleg is not None else 'null' for beleg in ergebnis]
ergebnis = ' '.join(ergebnis)
belegtypen_ergebnis = ' '.join(belegtypen_ergebnis)

return f"{ergebnis}\n{belegtypen_ergebnis}"

Beispielaufruf

tabelle = {
‘R014931’: ‘L013119’,
‘L013119’: ‘X010899’,
‘X010899’: ‘X009401’,
‘X009401’: None
}

ergebnis = belegsuche(tabelle, ‘R014931’)
print(ergebnis)

Die Funktion belegsuche nimmt die Tabelle und die gesuchte Belegnummer als Parameter. Sie verwendet eine Schleife, um den Quellbeleg für jede Belegnummer zu finden, bis ein None-Wert erreicht wird. Die entsprechenden Belegtypen werden aus einem Wörterbuch belegtypen abgeleitet und in den Ergebnissen gespeichert.

Für das gegebene Beispiel wird die Ausgabe wie folgt sein:

R014931 L013119 X010899 X009401 null
Rechnung Lieferschein Auftrag Auftrag Gutschrift

Die erste Zeile zeigt die Belegnummern, die durch Leerzeichen getrennt sind, und die zweite Zeile zeigt die entsprechenden Belegtypen.

Bitte beachten Sie, dass dies nur ein Beispiel ist und je nach Anforderungen und Datenstruktur der tatsächliche Code angepasst werden kann. Ich würde Ihnen auch raten, die Hilfe von Experten für Softwareentwicklung https://tech-stack.com/services/custom-software-development in Anspruch zu nehmen, da ich vielleicht irgendwo einen Fehler gemacht habe.

10

Wieso muss es überhaupt eine Zeile als Ergebnis sein? Wieso kann nicht der Listencharakter der Ursprungstabelle erhalten bleiben?
Das würde im übrigen ermöglichen, die Treffer mit Spalten wie Eingangsdatum / Ausgangsdatum, Sachbearbeiter, … zu erweitern.
Man muss bereits für die führende Typisierung ziemlich kurbeln.

Als Idee zur Belegart: Man könnte jeweils einen Functional Index auf die Spalten legen und immer nur nach ? suchen. Man will ja an der Stelle sowieso alles finden.

11

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.