recursive Common Table Expressions (RCTE)

[hoffkmann]

Hallo!
Mein Ziel ist es, mit SQL eine Liste von Stoffen und deren Inhaltsstoffe (mengenabhängig) zu erstellen. Diese soll auf einer gestuften Ordnung basieren:

z. B. gibt es die Stoffe A, B, C, D, E, F und G.

Im Stoff A sind auf erster Ebene die Stoffe B C und D vorhanden. In Stoff B sind die Stoffe E und F enthalten und in Stoff E ist der Stoff G und der Stoff B eingeschlossen. Dieses Beispiel ergibt folgende Tabelle:
Tabelle 1

Stufe	Stoff
1	B
2	E
3	G
3	B
2	F
1	C
1	D

Ermittelt werden die Daten in meinem SQL mit Hilfe einer so genannten „rekursiven Common Table Expression (RCTE) “ und grundsätzlich ist das Ergebnis auch in Ordnung und die Abhängigkeiten zwischen den einzelnen Inhaltsstoffen sind korrekt dargestellt. D. h. die Stoffe sind der Zusammensetzung nach richtig untereinander geschrieben, genau wie im oberen Beispiel demonstriert wurde.
Dabei wurde die richtige Zuordnung der Stufen untereinander durch die Zeile
search depth first by id_pos, id_kopf set seqcol
ermittelt und eine Endlosschleife durch die Zeile
CYCLE id_kopf, id_pos SET cyclic_data TO '*' DEFAULT ' '
verhindert.

Nun zu meinem eigentlichen Problem:
Ich möchte innerhalb der einzelnen Stufe noch nach einem weiteren Feld sortieren. Dieses Feld heißt „lf_nr“ und ist den Stoffen im obigen Beispiel wie folgt zugeordnet:

Tabelle 2

Stufe	Stoff	lf_nr
1	B	2
2	E	1
3	G	2
3	B	1
2	F	2
1	C	3
1	D	1

Richtig sortiert würde das Ergebnis wie folgt aussehen:
(mein Versuch: order by seqcol, lf_nr)

Tabelle 3

Stufe	Stoff	lf_nr
1	D	1
1	B	2
2	E	1
3	B	1
3	G	2
2	F	2
1	C	3

Leider werden die Daten in meinem SQL wie in Tabelle 2 angezeigt. Wird das SQL mehrmals hintereinander ausgeführt, so verändert sich auch die Reihenfolge des Felds „lf_nr“ innerhalb der Stufen. Deshalb meine Frage:
Wie bekomme ich die gewünschte Sortierung nach Tabelle 3 zuverlässig bei jedem Aufruf des SQLs?
Vielleicht kann mir jemand helfen. Ich habe jetzt noch mein eigentliches SQL mit angehängt. Da die Logik der RCTEs immer die gleiche ist, denke ich, dass ein grober Fehler daraus ersichtlich sein könnte.

with temp (level, id_kopf, id_pos, lf_nr) AS
(select 0, id, 0, 0
from sstkop00
where id_sartst=-1 and id_skenrz=4924820
UNION ALL
select t.level+1, sstkop.id, sstpos.id, sstpos.laufendenr
from sstruk00 s1
join temp t on (t.id_kopf=s1.id and t.id_kopf>0 and s1.BEZ='SSTKOP' and s1.id_sstatu=400001)
join sstkop00 kop on (kop.id=t.id_kopf)
join sstruk00 s2 on (s2.id_sstruk=s1.id and s2.id_sstatu=400001)
join sstpos00 sstpos on (sstpos.id=s2.id)
left join sstkop00 sstkop on (sstkop.id_sartst=sstpos.id_sartst and sstkop.id_skenrz=sstpos.id_skenrz)
left join sstart00 sstart on (sstart.id=sstkop.id_sstart)
where (f_scanconfig('INTERN_KEY', sstart.config))<>'E' or sstart.id is null
order by 4 asc)
search depth first by id_pos, id_kopf set seqcol
CYCLE id_kopf, id_pos SET cyclic_data TO '*' DEFAULT ' '
select
tmp.level, tmp.lf_nr,
skenrz.rezeptur, sartst.softmartikelnr,
tmp.id_kopf, tmp.id_pos, cyclic_data
from temp tmp
join sstpos00 pos on (pos.id=tmp.id_pos)
join sartst00 sartst on (sartst.id=pos.id_sartst)
join skenrz00 skenrz on (skenrz.id=pos.id_skenrz)
join smngeh00 smngeh on (smngeh.id=pos.id_smngeh)
join spdtkl00 spdtkl on (spdtkl.id=sartst.id_spdtkl)
order by seqcol, tmp.lf_nr;

Gruß
Kathrin

[Bernd Wiezroek]

Die laufnr ist sekundäres Sortierkriterium.

Also bei gleichem 1 Kriterium.

Wenn sie aber wie der Post nahe legt veränderlich ist kann sie nicht sortiert werden.

Evtl. kann man das erste Ergebnis zwischenspeichern und dann einfach sortiert ausgeben.

[hoffkmann]

Hallo!
Vielen Dank für Deinen Hinweis. Jetzt hätte ich allerdings noch ein paar Fragen, um ein Missverständnis auszuschließen.
Was genau meinst Du mit veränderlich? Die laufnr ist jedem Datensatz fest zugeordnet, also nicht veränderlich. Nur das Ergebnis, welches mir das SQL liefert, ändert seine Sortier-Reihenfolge bei mehrmaligem Aufruf. D. h. die Sortier-Reihenfolge der Stufen 1, 2 und 3 ändert sich, es bleibt nur die Zuordnung der Unterstufen innerhalb der übergeordneten Stufen richtig (Tabelle 2 und 3 könnten also beide Ergebnisse aus SQL sein).
An ein Zwischenspeichern habe ich auch schon gedacht. Dies fordert allerdings ein Auslesen mit Hilfe eines Programms, da durch eine Sortierung im SQL mit „order by“ die Zuordnung innerhalb der Stufen zerstört wird. Ich sehe leider keine Möglichkeit, mir diese Abhängigkeit mit einer oder mehreren zusätzlichen Spalten anzuzeigen.>>

[BenderD]

Hallo,

Tabelle 3 ist mit SQL nach der ANSI Definition von SQL nicht erzeugbar.
Ohne order by überlässt man die Reihenfolge der Database engine, jede angezeigte Sequenz ist also in dem Sinne zufällig, dass sie sich spätestens mit einem PTF ändern könnte (oder sogar at random, je nach gewählter Zugriffs Strategie) und die gewünschte Reihenfolge ist mit order by nicht darstellbar.
Wenn solche Daten in fester Sequenz gewollt werden braucht SQL ein zusätzliches Sortierfeld.

mfg

Dieter Bender