SQL

[niki]

Hallo,
kann man in einer SQL-Abfrage unter AS/400 nach Subselect sortieren:

Select Spalte1,
(Select Spalte from Tabelle1 where Spalte3=Spalte1) as Spalte2
From Tabelle2
Order by Spalte2
Vielen herzlichen Dank im voraus!

Niki

[B.Hauser]

Hi Niki,

Eine Möglichkeit ist die Spalten-Nr. im Order By anzugeben.

Das folgende Beispiel verknüpft eine Auftrags-Kopf und eine Auftrags-Positions-Datei. Pro Auftrag wird die Anzahl der verschiedenen Artikel ausgewiesen. Sortiert wird nach Anzahl der Artikel.

select h.Order_Number, (select count(Product_Number)     
                          from Order_Detail d                     
                          where h.Order_Number = d.Order_Number   
                          group by d.Order_Number) as Anzahl      
  from Order_Header h                                    
  order by 2 

Eine andere Möglichkeit wäre statt des Sub-Selects eine Common Table Expression zu verwenden

with a as (select d.Order_Number, count(Product_Number) as Anzahl 
             from Order_Detail d                                  
             group by d.Order_Number)                             
select h.Order_Number, Anzahl                                     
  from    Order_Header h join a 
       on h.Order_Number = a.Order_Number                                            
  order by Anzahl 

Birgitta

[BenderD]

Hallo Niki,

die ORDER BY Klausel wird mit als letztes ausgewertet, sprich:
- man kann nur einmal ORDER BY sagen
- ORDER BY wird auf der Ebene des Full Selects angegeben
- es kann nur nach Feldern sortiert werden, die in der Ergebnistabelle vorhanden sind
- woher diese Felder gefüllt werden ist völlig Schnurz

mfg

Dieter Bender

Hallo,
kann man in einer SQL-Abfrage unter AS/400 nach Subselect sortieren:

Select Spalte1,
(Select Spalte from Tabelle1 where Spalte3=Spalte1) as Spalte2
From Tabelle2
Order by Spalte2
Vielen herzlichen Dank im voraus!

Niki

[B.Hauser]

- es kann nur nach Feldern sortiert werden, die in der Ergebnistabelle vorhanden sind

Meinst Du damit ausgewählte Felder im Select statement?
Wenn ja, so ist das überholt.

Seit Release V5R1M0 ist es möglich auch nach Feldern zu sortieren, die nicht im Select aufgelistet sind.

Das folgende Beispiel funktionniert:

select h.Order_Number, (select count(Product_Number)      
                 from Order_Detail d                      
                 where h.Order_Number = d.Order_Number    
                 group by d.Order_Number) as Anzahl       
  from Order_Header h                                     
  order by Customer_Number, Order_date, 2 

Birgitta

[BenderD]

Hallo,

mag sein, wüsste nicht wofür das gut sein soll.

mfg

Dieter

Meinst Du damit ausgewählte Felder im Select statement?
Wenn ja, so ist das überholt.

Seit Release V5R1M0 ist es möglich auch nach Feldern zu sortieren, die nicht im Select aufgelistet sind.

Das folgende Beispiel funktionniert:

select h.Order_Number, (select count(Product_Number)      
                 from Order_Detail d                      
                 where h.Order_Number = d.Order_Number    
                 group by d.Order_Number) as Anzahl       
  from Order_Header h                                     
  order by Customer_Number, Order_date, 2 

Birgitta

[niki]

Hallo Niki,

die ORDER BY Klausel wird mit als letztes ausgewertet, sprich:
- man kann nur einmal ORDER BY sagen
- ORDER BY wird auf der Ebene des Full Selects angegeben
- es kann nur nach Feldern sortiert werden, die in der Ergebnistabelle vorhanden sind
- woher diese Felder gefüllt werden ist völlig Schnurz

Hallo Dieter,

Dafür muüsste meine Abfrage:
Select Spalte1,
(Select Spalte from Tabelle1 where Spalte3=Spalte1) as Spalte2
From Tabelle2
Order by Spalte2
richtig funktionieren. Aber wenn ich sie ausführe, wird nicht nach Spalte2 sortiert.

Gruß

niki

[BenderD]

Hallo Niki,

dann hat die Query Engine einen Schuss und hoffentlich gibt es dafür ein PTF (Database Group PTF). Bei mir unter V5R1 gibt es einen SQL Fehler, das heißt der Parser findets ok und die Query Engine fährt in den Wald.
Üblicherweise würde man dies Anfrage allerdings eher als Join formulieren und vielleicht haben die deshalb diesen Bug noch nicht gemerkt.

select a.Spalte1, b.Spalte
from Tabelle1 b, Tabelle2 a
where Spalte3 = Spalte1
order by b.Spalte
sollte für den Syntax Parser weniger stressig sein und das sollte er hinkriegen.

mfg

Dieter Bender

Hallo Dieter,

Dafür muüsste meine Abfrage:
Select Spalte1,
(Select Spalte from Tabelle1 where Spalte3=Spalte1) as Spalte2
From Tabelle2
Order by Spalte2
richtig funktionieren. Aber wenn ich sie ausführe, wird nicht nach Spalte2 sortiert.

Gruß

niki

[B.Hauser]

Hallo,

ich habe es gerade ausprobiert, unter Release V5R2 laufen Sclare Subselects inclusive Sortierung ohne Probleme.

Allerdings ist die Performance bescheiden, da bei Verwendung von Scalaren Subselects eine temporäre Ergebnis-Datei erstellt wird. Werden die Dateien direkt verknüpft entfällt dieser Schritt.

Ich vermute eher, dass der Sub-Select nicht korrekt ist.

Gibt es eine Fehlermeldung?
Hast Du geprüft, welcher Zugriffs-Pfad verwendet wird?

Birgitta

[BenderD]

Hallo Birgitta,

wenn es ohne ORDER BY geht und der ORDER BY was falsches liefert, dann liegt es nicht am SQL Statement, dann dürfte das ganze ohne ORDER schon nicht gehen.
Frei nach Theorie ist SQL Ergebnis orientiert und dem Optimizer sollte es zumutbar sein, den unechten SubSelect in einen Join aufzulösen, zumal wenn referential constraints den Join der Datenbank bekannt machen (wie bei mir, versteht sich). Die schlechte Performance bei euch deutet darauf hin, dass das DBMS hier immer noch eklatante Schwächen hat.
Sprich: Bug, nicht Feature.

mfg

Dieter Bender

PS: Wenn wir gerade beim probieren waren, ORDER BY Feldern, die nicht in der Ergebnis Tabelle liegen, geht bei V5R1 noch nicht.

Hallo,

ich habe es gerade ausprobiert, unter Release V5R2 laufen Sclare Subselects inclusive Sortierung ohne Probleme.

Allerdings ist die Performance bescheiden, da bei Verwendung von Scalaren Subselects eine temporäre Ergebnis-Datei erstellt wird. Werden die Dateien direkt verknüpft entfällt dieser Schritt.

Ich vermute eher, dass der Sub-Select nicht korrekt ist.

Gibt es eine Fehlermeldung?
Hast Du geprüft, welcher Zugriffs-Pfad verwendet wird?

Birgitta

[B.Hauser]

Hallo Birgitta,

Frei nach Theorie ist SQL Ergebnis orientiert und dem Optimizer sollte es zumutbar sein, den unechten SubSelect in einen Join aufzulösen, zumal wenn referential constraints den Join der Datenbank bekannt machen (wie bei mir, versteht sich). Die schlechte Performance bei euch deutet darauf hin, dass das DBMS hier immer noch eklatante Schwächen hat.
Sprich: Bug, nicht Feature.

mfg

Dieter Bender

PS: Wenn wir gerade beim probieren waren, ORDER BY Feldern, die nicht in der Ergebnis Tabelle liegen, geht bei V5R1 noch nicht.

1. Dass der Optimizer die Selects auflösen kann hatte ich auch gedacht, ist aber wohl nicht so. Ich habe beide Statements über den iSeries Navigator analysiert. Die Abfrage mit dem scalaren Select braucht die 3-fache Zeit, wie der Join. Beim Join wird keine temporäre Datei erstellt, da der Optimizer dieses Statement auflösen kann. Für beide Dateien können optimale Zugriffs-Wege ermittelt werden. Ausserdem werden die Dateien bei der Ausführung vertauscht, d.h. die mit der geringeren Satz-Auswahl wird zuerst verarbeitet.
Beim Scalaren Subselect dagegen kann nur für die erste Datei ein optimaler Zugriffs-Weg ermittelt werden. Für die zweite Datei wird ein Table Scan ausgeführt. Ausserdem wird eine temporäre Datei aus folgendem Grund erstellt:
2 - Die Abfrage enthält Sortierfelder (ORDER BY) aus mehr als einer Datei, oder sie enthält Sortierfelder aus einer Sekundärdatei von einer Verknüpfungsabfrage, die nicht neu sortiert werden können.

(Vielleicht ist das ja unter V5R3 bereinigt, unter V5R2M0 jedenfalls noch nicht!).
Und das hat mit unserer Datenbank bzw. unseren Dateien nichts zu tun! Es handelt sich ja nur um eine einfache 1:1 Beziehung und alle Zugriffswege sind angelegt.

2. Order By nach Feldern, die nicht aus der Auswahl-Liste sind gilt ab V5R2.

Und ... es gibt genügend Situationen, in denen dies durchaus Sinn macht.
z.B. Sortierung unabhängig von Gross- und Kleinschreibung zu gehen (die Sort Sequence kann darf man nicht unbedingt global ändern) oder Sortierung nach Datum, aber das Datum muss alphanumerisch im Format Tag/Monat/Jahr aufbereitet werden oder eine Pickliste muss wegeoptimiert gedruckt werden die Sortierung die bei der Optimierung festgelegt wurde ist uninteressant. Und dann gibt es noch jede Menge Kundenwünsche, z.B. Drucken einer Rechnung ohne Bestell-Positions-Nr., aber Sortierung danach oder Kunde möchte seine Aufträge nach Postleitzahl Liefer-Adresse sortiert, aber die Postleitzahl nicht angezeigt, weil er ja seine Kunden kennt u.v.m

Birgitta