SQL Date Between möchte einfach nicht gelingen

[dholtmann]

Stimmt, Satz 10 sei fehlerhaft!
Dankeschön!
Kann es helfen, in der View dann per where alle Sätze auszuschließen in denen das Datum ungültig ist bzw in den Fällen ein Standard Datum zu vergeben?

[BenderD]

... das Problem ist, dass der Query Optimizer, je nach Ausführungsplan, die Ausführungsreihenfolge ändert und dann über diese Sätze fällt. Gegenwärtig kriegt man das noch weg, wenn man die Datums-Verdreherei in eine Function auslagert und da den Ersatzwert zurückgibt - in die Function guckt die Query Engine nicht rein. Da gibt es, soweit ich das erinnere eine universelle Datumsfunction auf think400.dk, die alles mögliche kann.

D*B

[B.Hauser]

Ich würde eine UDF (User Defined Function) definieren und diese in die View einbinden, anstatt zu versuchen das Datum manuell zu konvertieren.
In der UDF kann ein ungültiges Datum durch einen Continue Handler abgefangen werden und statt des falschen Werts ein Dummy Datum (z.B. 01.01.0001 oder 31.12.9999 oder was auch immer) ausgegeben werden.
UDFs können in SQL wie skalare Funktionen eingesetzt werden.
(Es soll übrigens auch Firmen geben, die SQL und SQL-Programmierung schulen)

Die folgende UDFs empfängt ein numerisches Datum im Format JJJJMMTT und konvertiert dieses in ein echtes Datum.
Im Fehlerfall, also bei einem ungültigen Datum wird der 01.01.0001 ausgegeben.

CREATE OR REPLACE FUNCTION YourSchema.CVTNUMTODATE 
                  (PARDATENUM DECIMAL(8, 0)) 
	RETURNS DATE   
	LANGUAGE SQLDETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
        RETURN DATE ( '0001-01-01' ) ; 
  
RETURN DATE ( DIGITS ( PARDATENUM ) CONCAT '000000' ) ; 
END  ;

Birgitta

[Fuerchau]

Die Frage ist ggf. ob das Datum erkennbar falsch ist (z.B. 0 oder 99999999).
In diesem Fall kann man das auch mit einem CASE-Ausdruck lösen und den Default oder NULL zurückgeben.

Je nach dem wie die 3 Felder definiert sind hilft ggf. auch einfach dieses:
cast('0001-01-01') as date + (JJFeld -1) years + (MMFeld - 1) months + (TTFeld - 1) days

Wenn das JJFeld 2-stellig ist, kannst du das mit einem CASE wieder geradebiegen bzw. das Startdatum anpassen. Dann gibts auch mit dem Optimizer kein Problem.

Bei der Performance mit der UDF ist das nämlich so eine Sache, insbesonders wenn man auf die UDF noch eine Whereklausel hat, da zieht nämlich kein Index.

[BenderD]

... wenn's um Performance geht, hilft bei diskreten Werten eine Umsetztabelle, wenn das ganze in einem Programm passiert, ist es oft vorteilhaft Selektion per Extrakte (temp tables) vorzuziehen...
D*B

[Fuerchau]

Ich weiß auch nicht, ab wann der Optimizer seine Verfahren geändert hat (ich meine aber erst ab V6R1), dass Berechnungen der Select-Felder vor der Where-Klausel durchgeführt werden.
Mir ist es nämlich passiert, dass SQL's von V5 nach V6 nicht mehr funktionierten.
Per Where-Klausel hatte ich fehlerhafte Daten ausgefiltert um dann die korrekten Daten per CAST o.ä. anzupassen. Dies funktionierte nicht mehr, da der CAST häufig, aber nicht immer, vor dem Where ausgeführt wird.
Dies führt eben dazu, dass man verstärkt in seinen Berechnungen nun auch die Prüfungen der Where-Klausel wiederholen muss.
Aber auch hier ist die Reihenfolge der Ausführung eines Ausdruckes nicht gewährleistet, so dass es trotzdem zu Cast-Fehlern kommt obwohl man sie doch eigentlich per Case-Ausdruck ausgeschlossen hat. Hier hilft dann wirklich nur noch eine UDF.

Bei komplexen Ausdrücken hilft manchmal die NULL-Regel:
Ist ein Teilausdruck NULL wird der Gesamtausdruck NULL.

Beispiel:
coalesce(substr(MyFeld, 1, nullif(position('-' in MyFeld) - 1, -1)), MyFeld)

[BenderD]

... bei den Huddeldatumsfeldern (und um die geht es wie hier meist) verstehe ich die ganze Aufregung nicht. Eine einzige Umsetztabelle mit 36.500 Sätzen deckt 100 Jahre ab, was wohl den meisten reichen würde und von der Satzanzahl ist das für heutige AS/400 so gut wie nix. Da habe ich dann als Primary key ein Feld vom Typ Date und für jedes Huddelformat ein Feld mit korrespondierendem Wert. Damit da nicht nur Huddel drinsteht, kann ich gleich noch Felder für Vormonat, Ultiomo etc. mit aufnehmen. Habe ich Huddel und brauche ein ordentliches Format, wird die Tabelle gejoined - et voila...

D*B

[Fuerchau]

Das Datum ist leider nicht das einzige Problem.
Früher klappte folgender SQL:

select dec(myFeld, 10, 0) from myfile where myFeld <> ''

myFeld enthält entweder Leerzeichen oder eine Zahl.
Heute kann es eben passieren, das "dec()" schon mal durchgeführt wird und der Where-Ausschluss erst danach kommt.
Da hilft auch nicht unbedingt ein "case when myFeld = '' then null else dec(myFeld, 10, 0) end", da ggf. alle Teilausdrücke schon mal ausgewertet werden.
Daher halt den Umkehrschluss:
"dec(case when MyFeld = '' then null else MyFeld end, 10, 0)"

Zu beachten ist, dass es egal sein muss in welcher Reihenfolge der Ausdruck berechnet wird um ohne Fehler weiter zu kommen.
Weiteres Beispiel:

case F2 when 0 then null else F1 / F2 end

Auch hier ist eben nicht garantiert, dass die Division nicht bereits vor dem Case ausgeführt wird.
coalesce(F1 / nullif(F2, 0), 0) garantiert aber das gewünschte Ergebnis, da nicht durch NULL dividiert wird.
Usw. usf.

Übrigens alles Erfahrungen aus meinem BI-Leben wo eben die fehlende Datenqualität nicht zum Absturz eines SQL's führen darf. Gerade bei ODBC liefert die AS/400 dann u.U. Teilergebnisse oder sogar falsche Ergebnisse, da im ODBC alle Felder einen NULL-Anzeiger haben, aber die Differenzierung zwischen tatsächlich NULL (-1) oder TRUNCATE (-2) auf Feldebene nicht mehr getroffen werden kann.
Der ODBC-Treiber wertet nämlich nur "-1" aus als NULL aus.

[B.Hauser]

Das Datum ist leider nicht das einzige Problem.
Früher klappte folgender SQL:

select dec(myFeld, 10, 0) from myfile where myFeld <> ''

myFeld enthält entweder Leerzeichen oder eine Zahl.
Heute kann es eben passieren, das "dec()" schon mal durchgeführt wird und der Where-Ausschluss erst danach kommt.
Da hilft auch nicht unbedingt ein "case when myFeld = '' then null else dec(myFeld, 10, 0) end", da ggf. alle Teilausdrücke schon mal ausgewertet werden.
Daher halt den Umkehrschluss:
"dec(case when MyFeld = '' then null else MyFeld end, 10, 0)"

Zu beachten ist, dass es egal sein muss in welcher Reihenfolge der Ausdruck berechnet wird um ohne Fehler weiter zu kommen.
Weiteres Beispiel:

case F2 when 0 then null else F1 / F2 end

Auch hier ist eben nicht garantiert, dass die Division nicht bereits vor dem Case ausgeführt wird.
coalesce(F1 / nullif(F2, 0), 0) garantiert aber das gewünschte Ergebnis, da nicht durch NULL dividiert wird.
Usw. usf.

Übrigens alles Erfahrungen aus meinem BI-Leben wo eben die fehlende Datenqualität nicht zum Absturz eines SQL's führen darf. Gerade bei ODBC liefert die AS/400 dann u.U. Teilergebnisse oder sogar falsche Ergebnisse, da im ODBC alle Felder einen NULL-Anzeiger haben, aber die Differenzierung zwischen tatsächlich NULL (-1) oder TRUNCATE (-2) auf Feldebene nicht mehr getroffen werden kann.
Der ODBC-Treiber wertet nämlich nur "-1" aus als NULL aus.

Zur Abwechslung muss ich mal Dieter recht geben:
Huddelduddel bleibt Huddelduddel.
Und diese Beispiele sind nicht richtig durchdacht und unlogisch und damit wie Dieter so schön sagt "Huddelduddel". Da braucht man sich nicht zu wundern, dass manches irgenwann mal anders läuft als man sich zurechtgezimmert hat.

1. Man versucht ein Feld das numerische Werte oder Blanks beinhaltet in numerische Werte umszusetzen. Vielleicht hätte man an dieser Stelle zunächst versuchen sollen die Daten dahingehend zu korrigieren, dass entweder das Blank durch eine Null oder einen echten NULL Wert ersetzt wird bevor man versucht zu konvertieren.
Das bedeutet nicht, dass man den Wert physische ändern muss, man kann diese Konvertierung auch innerhalb des Statements (aber an der richtigen Stelle durchführen).
Also: Dec(Case When Feld = '' Then '0' Else Feld End, 11, 0)
oder Dec(NULLIF(Feld, ''), 11, 0)

2. case when myFeld = '' then null else dec(myFeld, 10, 0) end genau das ist der falsche Ansatz. Zuerst muss myFeld so konvertiert werden, dass es einen numerischen Wert enthält, der dann konvertiert wird ... also:
Dec(NULLIF(MyFeld, ''), 11, 0)

Das gleiche gilt für Case F2 When 0 Then NULL else F1/F2 End. In diesem Beispiel wird zunächst dividiert und dann das Ergebnis konvertiert, was nur schiefgehen kann.
Zunächst muss F2 konvertiert werden und dannach erst darf die Division erfolgen, also:
F1/Case When F2 = 0 then NULL Else F2 End
oder
F1/NULLIF(F2, 0)

Eigentlich völlig logisch! Warum beschwert man sich, dass genau nach dieser Logik vorgegangen wird?

Das Problem ist nicht die fehlende Datenqualität, sondern der unsachgemäße und falsche Einsatz der skalaren Funktionen.
Nach meiner Erfahrung kann man alles (an der richtigen Stelle) prüfen und entsprechend konvertieren (ODBC oder nicht).
Mit den geeigneten Views, kann man die Konvertierungen wunderbar verstecken, so dass diese nur an einer einzigen Stelle erfolgen.

Ich hatte übrigens bei Konvertierungen von alpha nach numerisch nie Probleme (und hatte natürlich auch solche Fälle in denen Blanks in Spalten standen bzw. durch einen Wert dividiert werden musste).

Birgitta

[BenderD]

... das Problem bei komplexen SQL Abfragen ist die Reihenfolge der Ausführung der Bestandteile des Statements in Abhängigkeit von der gewählten Zugriffsstrategie. Da kann ich mir sauber ausgedacht haben, bestimmte Sätze in der where Klausel wegzuschmeißen und der Query Optimizer entscheidet sich; da mache ich eine Zwischentabelle, über die ich dann einen full tablescan mache...
Das kann selbst dann passieren, wenn ich Bedingungen, die nur für eine Tabelle gelten in die join Klausel packe (was man immer tun sollte); auch da gibt es noch den Aetsch Zweig des Query Optimizers (da mache ich einen cross join und dann einen full table scan...)

Verschärft hat sich das Problem durch komplexere Statements und Verbesserungen der Query Engine in Folge schnellerer Hardware: je schneller die Hardware, umso eher sind Full table scan und temporäre Index by the fly von Vorteil (oder werden dafür gehalten).

Birgittas Vorschlag das in eine View zu verschieben, klingt gut, funktioniert aber genauso wenig wie ein CTE (common table Expression), da der Query Optimizer die Freiheit hat, die Bedingungen der View in das SQL Statement aufzunehmen - und dann bin ich sogar weiter weg von der Lösung (da man nicht direkt sieht, was in der View an Bedingungen und Konvertierungen steht). Es gibt in SQL keine stringente Möglichkeit der Query Engine eine Reihenfolge der Auswertung vorzuschreiben! (momentan geht noch die Verlagerung in eine stored Procedure oder eine UDTF, sobald der Optimizer da rein guckt, um das noch zu optimieren...) Selbst wenn der Query Optimizer nicht im Recht ist, weil die Umwandlung des SQL Statements nur fast äquivalent war, hilft mir das wenig und mit der Komplexität nimmt die Wahrscheinlichkeit von Bugs in der Query engine zu.

Huddelfreie Daten helfen Probleme zu vermeiden, aber auch das nicht immer (wenn z.B. ein Fulltable scan über einen cross join einfach zu langsam wird...) und viel zu oft hat man auch strukturellen Huddel in den Daten.

Macht man BI in einem "echten" Daten-Karstadt (AKA DWH), sollte das der ETL Prozess leisten (das T steht für Transform und da wird der Müll aus den Daten raussortiert, bzw. zu verwertbaren Daten recycelt). Versucht man BI auf Rohdaten, muss man Teilbereiche denormalisieren, im einfachsten Fall, indem man Extrakte zieht, die man dann weiterverarbeitet (aus einem SQL Statement werden dann mehrere, die temp tables erstellen, die man weiterverarbeitet), damit löst man viele Problem, aber nicht alle...

Die zweite Technik sind dann statische Hilfstabellen (habe ich am Beispiel einer Zeit Hierarchie in einem vorherigen Beitrag erläutert); das geht aber nur bei Wertebereichen mit überschaubarer Kardinalität so einfach.

Die nächste Stufe sind dann gleichartige Hilfstabellen, deren Größe man dadurch im Zaume hält, dass man nur das reinnimmt, was tatsächlich vorkommt (damit kann man selbst abenteuerliche Problem, wie numerische Daten in Alphafeldern, in denen noch eins, zwei drei oder Zahlen plus Währungsbeträge oder null komma nix drinsteht heilen). Da erstellt man vorab eine bereinigte Umsetztabelle, die man dann joined; will man die immer adhoc haben, kann man die auch per Trigger auf die Originaltabelle mit pflegen und aktuell halten.

D*B

[Fuerchau]

Was soll man machen, wenn man auf Daten aus 20-jährigen ERP-Systemen auswerten will?
Ich habe ja gerade bei diesen ETL-Prozeduren aus verschiedensten Datenbanken genau diese verschiedenen Probleme.
Und da man sich halt auf den Optimizer nicht verlassen kann und der sowieso macht was er will muss man sich halt mit den Problemen herumschlagen.
Man kann auch nicht einfach so die Datenbasis ändern, denn immerhin läuft das ERP noch immer so und arbeitet eben nicht mit SQL. Und wenn es dann sog. allgemeine Tabellen gibt, die diverseste Daten aufnehmen so ist man halt bei ETL-Prozessen auf entsprechende SQL-Logik angewiesen.
Sicherlich muss man bei SQL lernfähig sein, denn was eben vor 10-15 Jahren galt führt eben heute zu Problemen.
Auf V5R1 habe ich eben schon mit SQL "rumgemacht" und es funktionierte eben ohne jedwede Veränderung bis V5R4. Erst der Wechsel dann auf V6R1 hat dank des Optimizers zum Absturz der SQL's geführt da der nun von nun an mal einen anderen Weg eingeschlagen hat.
Hätte ich mein Wissen von heute bereits vor 10 Jahren einsetzten können, wären diese Probleme eben nicht aufgetreten.
Da man kann gerne mit modernem Wissen daherkommen, wenn es dann vor 10 Jahren auch schon so gewesen wäre.
Und wer weiß, ggf. werden reine SQL-Prozeduren/Funktionen auch noch mal (wie Dieter schon sagte) optimiert.

[Fuerchau]

Nachtrag:
Und gerade was ETL angeht so ist es durchaus üblich, auf dem Quellsystem nichts ändern zu dürfen.
So schön das erstellen einer View oder UDF/UDT, einer Hilfstabelle auch wäre, es ist nicht erlaubt und wird auch nicht bezahlt. Ich kann dem Kunden nicht sagen, na dann, dann kann ich die Daten eben nicht auswerten.
Irgendeine Lösung gibt es immer.

@Birgitta
Ich habe mich ja nicht beschwert sondern nur darauf hingewiesen, dass man bei seinen Casts und Berechnungen eben genau das machen muss was du mir ja geradezu bestätigst.
Warum wiederholst du mich also nur und stellst es andersherum dar?
Und ich wollte eben nur darauf hinweisen, dass der Optimizer eben je nach Release und Lust und Laune anders entscheidet.
Bei einem ERP-System haben wir diverse UDF's generiert, die Standardfunktionalitäten der ERP-Software zur Verfügung stellten. Bis V6R1 war auch alles gut.
Seit V7R1 interressiert sich aber der Optimizer nicht mehr für Zwischensortierungen (also order By in CTE/Derived Tables). Diese kann man nun auch durchaus getrost vergessen.
Einzige Ausnahme sind scalare Subselects mit "Order by" und "Fetch first 1 rows only", diese funktionieren nach wir vor.
Ich war daher gezwungen, die UDF's mit eigenen Cachefunktionen zu ergänzen um immer noch performant arbeiten zu können.
Wie gesagt, je nach Release stoße ich halt immer mal wieder auf Neuerungen des Optimizers, die zu Verschlechterungungen bisheriger SQL's führen.
So extrem ist es leider erst seit V6R1, bis V5R4 waren die SQL's auch aus V4R3 noch kompatibel.