SELECT Abfrage Ergebnis um weitere Daten erweitern

motion · Beiträge: 295 XP, Linux D7 Prof

Eine Tabelle ExternePreislisten enthält Herstellernamen, Artikel-Nr., "umgeschlüsselt in",Artikelname, Preise etc.

folgender Select erzeugt schon die gewünschten Daten:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:

Select first(50)
       EXTHERSTELLER
     , ARTIKELNR
     , UMGESCHLUESSELT_IN
     , ARTIKELNAME
from ExternePreisliste
where artikelNr in ('201','791')
order by EXTHERSTELLER,ARTIKELNR

ergibt

markieren

Quelltext

1:
2:
3:
4:

EXTHERSTELLER  ARTIKELNR  UMGESCHLUESSELT_IN  ARTIKELNAME
CLAAS          201                            SCHEIBE      
CLAAS          791        8281810                          
KRAMP          201        00200001            Kolbenring

Okay, jetzt mein Problem:
Ich brauche die ArtikelNr. in "umgeschluesselt_in" ebenfalls.
Das Ergebnis soll also so aussehen:

markieren

Quelltext

1:
2:
3:
4:
5:
6:

EXTHERSTELLER  ARTIKELNR  UMGESCHLUESSELT_IN  ARTIKELNAME
CLAAS          201                            SCHEIBE      
CLAAS          791        8281810                          
CLAAS          8281810                        FEDERBUEGEL  
KRAMP          00200001                       Kolbenring   
KRAMP          201        00200001            Kolbenring

Kriege ich das in einem SQL irgendwie hingefummelt? UNION könnte ja gehen, aber wie kann ich da auf Ergebisse des ersten SQL zurück greifen?

BenBE · Verfasst: So 21.08.11 02:05

Wenn ich dein Problem richtig verstanden hab, könnte folgendes helfen:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select first(50)
    EXTHERSTELLER,
    ARTIKELNR,
    UMGESCHLUESSELT_IN,
    ARTIKELNAME
from ExternePreisliste
where artikelNr in ('201','791')
or artikelNr in (
    Select first(50)
        UMGESCHLUESSELT_IN
    from ExternePreisliste
    where artikelNr in ('201','791')
    )
order by EXTHERSTELLER,ARTIKELNR

Wobei das maximal 50 Datensätze liefert. Brauchst Du von den ersten 50 zusätzlich die weiteren Einträge (ungeachtet dessen, dass dann mehr als 50 Datensätze kommen könnten), sollte es so gehen

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select first(50)
    EXTHERSTELLER,
    ARTIKELNR,
    UMGESCHLUESSELT_IN,
    ARTIKELNAME
from ExternePreisliste
where artikelNr in ('201','791')

union

Select
    EXTHERSTELLER,
    ARTIKELNR,
    UMGESCHLUESSELT_IN,
    ARTIKELNAME
from ExternePreisliste
where artikelNr in (
    Select first(50)
        UMGESCHLUESSELT_IN
    from ExternePreisliste
    where artikelNr in ('201','791')
    )

order by EXTHERSTELLER,ARTIKELNR

Um doppelte Datensätze zu vermeiden, an geeigneter Stelle noch Distinct's einstreuen. Für eine Rekursive Auflösung werden IMHO Stored Procedures benötigt. In Oracle kann man alternativ hierarchische Selects nutzen. Wie da der Support für in Firebird aussieht, weiß ich grad nicht.

Durch zusätzliche Klammerung kannst Du natürlich auch die Sortierung noch anpassen, wenn nötig.

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

haentschman · Verfasst: So 21.08.11 09:58

Guten Morgen...

so einfach ist es nicht. Er möchte einen Datensatz in der Ergebnismenge haben der so nicht wirklich existiert. Es soll quasi die Spalte Umgeschlüsselt nach Artikelnummer verschoben sein.

Das Problem am union ist, daß die Spalten identisch sein müssen (Anzahl und Typ).

Ich hätte 3 Vorschläge:
1.
- via union die Datensätze holen

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

select * from (
select
    EXTHERSTELLER,
    ARTIKELNR,
    UMGESCHLUESSELT_IN,
    ARTIKELNAME
from ExternePreisliste
where artikelNr in ('201','791')

union

select
    EXTHERSTELLER,
    UMGESCHLUESSELT_IN as ARTIKELNR,
    UMGESCHLUESSELT_IN,
    ARTIKELNAME
from ExternePreisliste
where artikelNr in ('201','791')

) order by EXTHERSTELLER,ARTIKELNR

- danach die Ergebnismenge durchgehen und bei Datensätzen wo Umgeschlüsselt und Artikelnummer identisch sind die Spalte Umgeschlüsselt auf '' setzen.
- Achtung: wenn du die ID der Datensätze mitlädst tragen die manipulierten Datensätze auch die Original ID. In diesem Falle dann auch die ID eindeutig kennzeichnen, damit evt. Verwechslungen vermieden werden.

2. 2 Datenmengen holen
- holen der Artikel
- holen der umgeschlüsselten Artikel
- clientseitig Anhängen der umgeschlüsselten Artikel an die Artikel und den Wert Umgeschlüsselt bei Artikelnummer eintragen / Umgeschlüsselt leer lassen (Append oder AppendRecord)
- gleiches Problem mit ID des Datensatzes

3. Leerspalte in Tabelle hinzufügen
- Leerspalte in Tabelle und beim union select statt UMGESCHLUESSELT_IN die Leerspalte holen
- erspart das clientseitige Durchgehen

Frage: Sollte nicht beim Umschlüsseln ein Artikel mit der Artikelnummer (die umgeschlüsselte) vorhanden sein ? :gruebel:

Xion · Verfasst: So 21.08.11 10:11

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

with recursive rekTable as (
  select e.ArtikelNr as ArtikelNr, e.UMGESCHLUESSELT_IN as umg, 1 as RekTiefe
  from ExternePreisliste e
  where e.artikelNr in ('201','791')
  union all
  select e.ArtikelNr, e.umg, r.RekTiefe+1
  from rekTable r, ExternePresiliste e
  where r.umg = e.ArtikelNr
)

select *
from ExternePreisliste e, rekTable r
where e.artikelNr = r.artikelNr
order by e.externhersteller,e.ArtikelNr

So würde ich das mal probieren

_________________
a broken heart is like a broken window - it'll never heal
In einem gut regierten Land ist Armut eine Schande, in einem schlecht regierten Reichtum. (Konfuzius)

motion · Beiträge: 295 XP, Linux D7 Prof

@All: vielen Dank für die schnellen Anregungen und Lösungsvorschläge

@Xion: So ganz habe ich noch nicht geblickt, was das SQL genau macht, aber einfach mal in IB Expert ausgeführt bekomme ich nur diese Fehlermeldung:
"Too many concurrent executions of the same request.
Too many concurrent executions of the same request."

@haentschman:
1.) mit Programmlogik die Daten zu ermitteln ist nicht so gut. Das kriege ich so dann nur mühsam in der SQL Grid hinein
2.) Das könnte ich eventuell machen. In einem ersten Schritt nur die umgeschlüsseten Nummern holen (die ursprungs-Nr. habe ich ja) und dann in der eigentlichen HauptSQL die ursprungs-Nr. UND die umgeschlüsselten Nummern holen. Dann habe ich nur den 2. erweiterten SQL mit allen Artikeln. Das ist in der Programmlogik eigentlich drin.
3.) Das wäre auch eine Option eine temporäre Tabelle mit Zusatzspalte, auf die ich dann suche

Danke für die Anregungen nochmals. Da muss ich kurz noch mal drüber grübeln. Aber ich denke damit kann ich das hinkriegen.

haentschman · Verfasst: So 21.08.11 13:42

Zitat:

mit Programmlogik die Daten zu ermitteln ist nicht so gut.

... das meinte ich auch nicht. :zwinker:

Mit dem Beispiel SQL hättest du dann in Artikelnummer und Umgeschlüsselt den selben Wert stehen. In dem Umgeschlüsselten Datensatz soll ja das Umgeschlüsselt leer sein. Ich meinte nach dem Query.Open einmal alle DS durchgehen und schauen wo Artikelnummer und Umgeschlüsselt identisch sind und der Optik halber Umgeschlüsselt auf '' setzen.

... ich hoffe mich verständlich ausgedrückt zu haben :wink:

Zitat:

Das kriege ich so dann nur mühsam in der SQL Grid hinein

es ist und bleibt ja eine Query welche an ein DBGrid z.B. gebunden ist. Nur war der Gedanke vor dem Anzeigen die "Optik" der Spalte umgeschlüsselt mit setzen der Spalte auf '' anzupassen.

Xion · Verfasst: So 21.08.11 18:37

Also ich finde bei meinem keinen Fehler. Kann es sein dass du da Zyklen drin hast? Also das eins auf das andre umschlüsselt und dann (über mehrere Artikel evtl) wieder auf das Ausgangsprodukt? Weil die Fehlermeldung heißt wohl soviel wie Endlosschleife. Eventuell steht da eine 0 drin und du hast einen Artikel mit Nummer 0? Dann würde 0 auf sich selbst verweisen.

//Edit:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

with recursive rekTable as (
  select e.ArtikelNr as ArtikelNr, e.UMGESCHLUESSELT_IN as umg, 1 as RekTiefe
  from ExternePreisliste e
  where e.artikelNr in ('201','791')
  union all
  select e.ArtikelNr, e.UMGESCHLUESSELT_IN, r.RekTiefe+1
  from rekTable r, ExternePresiliste e
  where r.umg = e.ArtikelNr and r.RekTiefe<5
)

select *
from ExternePreisliste e, rekTable r
where e.artikelNr = r.artikelNr
order by e.externhersteller,e.ArtikelNr

Probiers mal so. Jetzt wird nach 5 Rekursionen gestoppt.

Funktionsweise:
Im ersten Teil (With) erstellst du eine temporäre Tabelle. Aus der liest du dann im unteren Teil aus.
Diese temporäre Tabelle wird rekursiv aufgebaut. Als erstes werden all die Artikel dazugenommen, welche die where Bedingung erfüllen (also ArtNr 201 und 791).
Danach wird (union all) diese grad erzeugte Tabelle mit 201 und 791 genommen, und all die Artikel hinzugefügt, bei denen (alt).Umgeschlüsselt = (neu).ArtikelNr.
Das wird solange gemacht, bis keine neuen Produkte mehr dazukommen (oder halt bei einer Schleife ewig...)

_________________
a broken heart is like a broken window - it'll never heal
In einem gut regierten Land ist Armut eine Schande, in einem schlecht regierten Reichtum. (Konfuzius)

motion · Beiträge: 295 XP, Linux D7 Prof

@Xion: Ahhh, der zweite Ansatz läuft durch und produziert das gewünschte Ergebnis. Aber auch noch einen Haufen Datensätze mit leeren Einträgen in ArtikelNr. Die sind aber schnell zu filtern.

Hier der (auch Schreibfehlerberichtigte SQL ohne die leeren Zeilen sowie auf die notwendigen Felder eingegrenzt):

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

with recursive rekTable as (
  select e.ArtikelNr as ArtikelNr, e.UMGESCHLUESSELT_IN as umg, 1 as RekTiefe
  from ExternePreisliste e
  where e.artikelNr in ('201','791')
  union all
  select e.ArtikelNr, e.UMGESCHLUESSELT_IN, r.RekTiefe+1
  from rekTable r, ExternePreisliste e
  where r.umg = e.ArtikelNr and r.RekTiefe<5 and e.artikelnr<>''
)

select first (50)  e.EXTHERSTELLER
     , e.ARTIKELNR
     , e.UMGESCHLUESSELT_IN
     , e.ARTIKELNAME
from ExternePreisliste e, rekTable r
where e.artikelNr = r.artikelNr
order by e.exthersteller,e.ArtikelNr

Das ergibt jetzt dieses hier:

markieren

Quelltext

1:
2:
3:
4:
5:
6:
7:
8:

EXTHERSTELLER  ARTIKELNR  UMGESCHLUESSELT_IN  ARTIKELNAME
CLAAS          201                            SCHEIBE      
CLAAS          201                            SCHEIBE      
CLAAS          791        8281810                          
CLAAS          8281810                        FEDERBUEGEL  
KRAMP          00200001                       Kolbenring   
KRAMP          201        00200001            Kolbenring   
KRAMP          201        00200001            Kolbenring

Schon ziemlich gut, so dass durch die Rekursive Suche auch mehrfache Umschlüsselungen erfaßt werden.
Lediglich die Dupletten müssen noch raus (z.B. Zeile 2 & 8 ).

Okay, da habe ich das distinct auch schon richtig gesetzt. So das daß finale SQL jetzt lautet:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

with recursive rekTable as (
  select e.ArtikelNr as ArtikelNr, e.UMGESCHLUESSELT_IN as umg, 1 as RekTiefe
  from ExternePreisliste e
  where e.artikelNr in ('201','791')
  union all
  select e.ArtikelNr, e.UMGESCHLUESSELT_IN, r.RekTiefe+1
  from rekTable r, ExternePreisliste e
  where r.umg = e.ArtikelNr and r.RekTiefe<5 and e.artikelnr<>''
)

select distinct e.EXTHERSTELLER
     , e.ARTIKELNR
     , e.UMGESCHLUESSELT_IN
     , e.ARTIKELNAME
from ExternePreisliste e, rekTable r
where e.artikelNr = r.artikelNr
order by e.exthersteller,e.ArtikelNr

und das Ergebnis ist wie gewünscht:

markieren

Quelltext

1:
2:
3:
4:
5:
6:

EXTHERSTELLER  ARTIKELNR  UMGESCHLUESSELT_IN  ARTIKELNAME
CLAAS          201                            SCHEIBE      
CLAAS          791        8281810                          
CLAAS          8281810                        FEDERBUEGEL  
KRAMP          00200001                       Kolbenring   
KRAMP          201        00200001            Kolbenring

Ich glaube das ist die Lösung. Vielen, vielen Dank. Dann brauche ich an der Programmlogik ja gar nichts zu ändern und nur den SQL anzupassen.
Obwohl, sosehr ich mich auch anstrenge: Was das SQL genau macht, durchschaue ich immer noch nicht :-)

BenBE · Verfasst: So 21.08.11 19:07

Kurze Beschreibung, was der macht, ist hier erklärt. Leider nur in der englischen; für die deutsche Wikipedia war das irrelevant.

_________________

Anyone who is capable of being elected president should on no account be allowed to do the job.
Ich code EdgeMonkey - In dubio pro Setting.

motion · Beiträge: 295 XP, Linux D7 Prof

Okay, das Stichwort lautet also "hierarchische queries". Okay, die sind ja seit Firebird 2.1 auch möglich (ich verwende 2.5), also werde ich mich mal einlesen in das Thema.
BTW: IBObjects mag den Query leider nicht, denn der interne Parser kommt da in Schwierigkeiten und modifiziert die Where-Clause leider falsch. Ich muß mal schauen, wie ich das umschiffe. Das ist aber kein reines SQL Problem mehr.

haentschman · Verfasst: So 21.08.11 19:57

hmmm... muß ich mich auch mal damit beschäftigen. :roll:

Was nicht alles geht...

motion · Beiträge: 295 XP, Linux D7 Prof

So, dem IBObjects habe ich das jetzt auch beigebracht. Mit OnMacroSubstitute puhle ich das dem System jetzt bei.
Funktioniert wunderbar!
Danke an Alle.

Xion · Verfasst: So 21.08.11 22:09

Du kannst jetzt auch "and r.RekTiefe<5" weglassen (damit du beliebig verschachtelte Umschlüsselungen verwenden kannst). Wobei du dann sicher stellen musst, dass es keine Schleifen gibt. Oder du schreibst da 25 hin und hoffst, dass es nie "tiefere" Umschlüsselungen gibt.

_________________
a broken heart is like a broken window - it'll never heal
In einem gut regierten Land ist Armut eine Schande, in einem schlecht regierten Reichtum. (Konfuzius)

motion · Beiträge: 295 XP, Linux D7 Prof

max.Rekursiontiefe habe ich so auf 7 festgelegt. Einige wenige Artikel sind im Laufe der Jahrzehnte so oft umgeschlüsselt worden.
Den SQL habe ich jetzt auch schon zwei Tage im Produktionsbetrieb und der funktioniert wie eine Eins. Mehrfachumschlüsselungen sind sauber aufgelöst.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme