SQL set oder select into mit dynamischer Selektion

[BenderD]

select count(*)
from ...
where ...
and col1 between :low1 and :high2
and col2 between :low2 and :high2
...

wenn in :lowx, :highx lowval bzw highval ist die Bedingung immer true
schreibe ich einen Wert in beide rein, nimmt er nur diesen

et voila!

mit dem null habe ich Bedenken, das sieht mir nach einer Fehlerbedingung aus (Vergleiche mit einem Nuulwert gehen nur mit is null, bzw. is not null

D*B

[dschroeder]

Die Lösung von Dieter Bender finde ich auch cool. Gefällt mir persönlich sogar am besten, weil sie
statisches SQL ermöglicht und ohne Nullwerte auskommt (ist ja in RPG oft ungewohnt).

(Die Lösung von Baldur habe ich immer noch nicht verstanden)

[Fuerchau]

Das mit dem NULL ist ja Absicht, da das Ergebnis nicht relavant, also ebenso nicht TRUE ist.
Da es ja eine Bedingung für den Ausschluss des dynamischen SQL's gibt, kann dieselbe Bedingung auch für das statische SQL gelten.

NULL und Null-Anzeiger:

Eine Where-Klausel will als Ergebnis ein WAHR haben, dabei wird boolsche Algebra ausgewertet.
Generell gilt: wenn ein Teil-Ausdruck NULL ist, wird der gesamte Ausdruck NULL.
folglich:

where Feld = : Parm : NullInd

ergibt: Wenn Parm nicht NULL ist (NullInd <> -1) und Feld = Parm ist, ist die Bedingung wahr.
Wenn du also NullInd auf -1 setzt ist das Ergebnis nicht Wahr, wenn du es auf 0 setzt ist das Ergebnis wahr.
Null-Idikatoren können nicht nur bei Results angewendet werden sondern auch bei Parametern um z.B. NULL beim Insert/Update zu schreiben.

NullInd ist aber nur nötig, wenn es keine andere Ausschlussbedingung gibt:
Also

where (: P1 = 0 or : P1 = Feld1 )
and (: P2 = '' or : P2 = Feld2 )
usw. usf.

[BenderD]

where Feld = : Parm : NullInd

wenn ich den nullind setze, steht da

where Feld = null

und das ist illegal. Selbst wenn es das durchlässt hätte ich Bedenken. Wenn das bei einem PTF oder release korrigiert wird, fällt das pgm auf die Schnauze.

[dibe]

Was mich verwirrt ist das OR

Der Anwender selektiert

Name: Hugo
Land: D
Verkäufer: 37
Artikel: --> egal
Farbe: --> egal
Größe: --> egal

Also ... where Name = 'Hugo' and Land = 'D' and Verkäufer = 37

oder in der Fuerchau Schreibweise
ind1 = 0
ind2 = 0
ind3 = 0
ind4 = -1
ind5 = -1
ind6 = -1
Na = 'Hugo'
LN = 'D'
VK = 37
AR =' '
FA = ' '
GR = 0

where (Name = :Na :ind1
or Land = :LN :ind2
or Verkäufer = :VK :ind3
or Artikel = :AR :ind4
or Farbe = :FA :ind5
or Größe = :GR :in6)


Das verstehe ich nicht.

Dietlind Beck

[Fuerchau]

Frage: Sind die Suchkriterien bei And oder bei Oder?
I.d.R. gelten Suchbedingungen mit And, also meine letzte Variante, da die Suchkriterien ja leer sind:

where ( :Na = '' or Name = :Na)
and (:LN = '' or Land = :LN )
usw.

Somit gilt: Wenn NA = Leer ist, spielt Name keine Rolle, wenn LN = leer ist, spielt Land keine Rolle, wenn beide nicht leer sind, muss beides stimmen.
Ds klappt auch mit 0-Werten (es sei denn das 0 zulässig ist, dann brauchst du den NullInd) oder Datum mit '0001-01-01' (*loval).
Die Kombination ergibt sich eben aus den Inhalten und das lässt sich beliebig fortsetzen.
Sogar Indizes können dann verwendet werden.
NULL wird dann gar nicht benötigt. Deine Fragestellung hat halt ein wenig verwirrt.

Wobei ich mich Frage, was du da mit einem Count(*) willst, da du i.d.R. ja ein Resultset willst.
Den Count über das Ergebnis kann man mit "count(*) over () AnzahlZeilen" bekommen.

[B.Hauser]

Wo liegt denn jetzt überhaupt das Problem?
Darin, dass die Spalten NULL fähig sind und falls keine Auswahl erfolgt ist alle Zeilen (unabhängig davon ob das Feld in der Zeile einen NULL-Wert enthält oder nicht) angezeigt werden sollen?

NULL-Werte müssen separat geprüft werden, da sie außerhalb des gültigen Bereichs liegen. Da NULL-Werte nicht direkt mit anderen NULL-Werten verglichen werden können, ist das Ganze ein bisschen tricky.
... aber die folgende Syntax sollte auch bei NULL-fähigen Spalten (im statischen SQL funktionieren), d.h. wenn in dem entsprechenden Feld (:HostCol) kein Wert erfasst wurde, werden alle Zeilen incl. den Zeilen, die in der entsprechenden Spalte NULL-Werte enthalten ausgegeben.

  Where     Col1   is not distinct from Case When :HostCol1 > '' Then :HostCol1 else Col1 End
        and Col1   is not distinct from Case When :HostCol2 >= -99999999 Then :HostCol2 Else Col2 End
        and MyDate is not distinct from Case When :HostCol3 > '0001-01-01' Then :HostCol3 Else Col3 End
        and ...

[Fuerchau]

Es ging nicht um die Frage wie NULL abgefragt wrd, dafür gibts ebenso "is null".
Es ging nur darum, bei bestimmten Abfragemethoden einen Parameter als NULL-Wert zu übergeben um die Abfrage ggf. ungültig zu machen.
NULL kann man im Zweifel auch mit einem Default vergleichen, was ich ebenso gerne mache:
coalesce(Feld, Default) = :Parameter

Aber, darum ging es in diesem Thread überhaupt nicht.
Deine komplizierte Abfrage ist da schon eher mit Dieters Beispiel "between" ablösbar.
Im IBM i Umfeld habe ich es konkret noch nicht erlebt, dass man NULL-Werte vernünftig verwendet.
In einer View wird dann lieber per coalesce ein NULL-Wert wieder ausgeschlossen, da man wohl in RPG nur bedingt damit umgehen kann.

Die Eingangsfrage kann mit dem simplen statischen Where in meinem Beispiel gelöst werden.
Simplify vs. complicated;-).

[B.Hauser]

Deine komplizierte Abfrage ist da schon eher mit Dieters Beispiel "between" ablösbar.

Schade, dass Dir mein Beispiel zu kompliziert ist!

Dieters Abfrage wird nur die Zeilen auswählen die auch einen echten Wert haben, jedoch nicht die Zeilen, die einen NULL-Wert haben.
Wenn Du also von *LOVAL zu *HIVAL alles selektierst gehen Dir die NULL-Werte durch die Lappen, d.h. Du musst zusätzlich eine IS NULL-Abfrage hinzufügen um alle Sätze zu bekommen!
... und genau das umgehe ich mit dem IS NOT DISTINCT FROM!
Die darauffolgende CASE-Anweisung sorgt nur dafür, dass entweder nur der übergebene Wert oder eben alle Werte (inkl. NULL-Werten) ausgewählt werden.

Ansonsten hätte schon mein erstest Beispiel mit der CASE-Anweisung wahlweise die ausgewählten Werte oder alle Zeilen zurückgebracht (da hätte man Deine Lösung, die i.Ü. Dieter Schröder gleich in der ersten Antwort gebracht hat, und die abgelehnt wurde nicht nochmal gebraucht!)
... und außerdem ist nicht jedes Eingabe-Feld alphanumerisch, was jeweils in Deiner Lösung auch korrekt berücksichtigt werden sollte!

Das war m.E. die ursprüngliche Frage, die anschließend nochmal korrigiert wurde, nämlich dahingehend, dass bei NICHT-Eingabe alles ausgewählt wird.
Was man durch ein bei 64 Auswahlen wahrscheinlich wesentlich komplizierteres dynamisches SQL lösen wollte.

Bei der (gnadenlosen) Konvertierung mit COALESCE (in den WHERE-Bedingung) kann nicht mehr sauber zwischen NULL-Wert und Default-Wert unterschieden werden.
Da in dem ursprünglichen Beispiel lediglich ein Count(*) ausgeführt wird, werden grundsätzlich alle Sätze gezählt und ein COALESCE ist unnötig. Bei nicht gefunden wird auch nichts ausgegeben!

... aber Du hast recht warum sollte man alles verkomplizieren (z.B. mit dynamischem SQL und/oder Indikator-Variablen, die dann in 64 IFs geprüft und genauso in 64 Zeilen gesetzt werden müssen) wenn man es auch einfach haben kann?

[Fuerchau]

Nun ja, er sprach von 64 Kombinationen und nicht von 64 Variablen.
Von NULL-Werten war nicht die Rede und es zeigt, dass du meine Variante nicht verstanden hast, denn ich wollte keine NULL-Werte abfragen.
Es wurde auch interpretiert, dass die Variablen Inhalte haben könnten, die aber nicht relevant wären, was man eben mit NULLInd lösen kann, da dann der Inhalt nicht zählt.
DISTINCT löst auch nur das Gleichheitsproblem, allerdings nicht Vergleiche mit Like, <, >, usw.

Was hast du also gegen (:P = ('' | 0) or Feld = :P), wobei das = eben auch mit allen anderen Vergleichen funktioniert. Ich habe sogar schon (:P = '' or Feld in Split(:P, ',')) ausgeführt um Mehrfachauswahlen zu lösen. P enthält dann eine kommaseparierte Liste von Werten.
Phänomenal das Ganze. Und jetzt schreib nicht wieder, dass es Split so nicht gibt. Das weiß ich ja.
Und wenn du NULL haben willst, füge doch einfach einen "or Feld is Null" hinzu.
Und bevor du fragst: ('' | 0) soll heißen typgerecht entweder '' oder 0.

Und was solls. Es ist doch einfacher einen statischen SQL zu bauen, der alle Variablen enthält und die Prüfung ob relevant ganz einfach ist als einen dynamischen SQL zu bauen, der auch auch die verschiedenen Varianten abfragen muss um ihn zu bauen und außerdem nicht mit Parametern sondern mit Stringkonstanten arbeiten muss, %edit, oder das O'Hara-Problemen lässt grüßen.
Auch CCSID-Probleme mit Unicode (1200) oder SBCS (1141) kommen noch dazu, was Parameter eben erleichtern. Du müsstest den prepared String nämlich besser als Unicode (1200) aufbauen.

Ich baue meine Abfragen schon seit Jahrzehnten genau so auf und erstaunlicherweise funktionieren sie auch noch schnell.

[BenderD]

... ich bin immer für einfache Lösungen: Nullvalues sind Datenbank interne Konstrukte und haben in den externen Sichten nix verloren. Die werden im Viewlayer rausmaskiert.

D*B

[dibe]

Vielen Dank für die vielen Ausführungen.
Unser Entwickler hat sich für die 'between' Variante entschieden.
Performance ist OK

Warum *Count!

Der Anwender kann in einem Subfile Im CTRL-Satz für jede Spalte einen Filter setzen
Ist der Filter leer, ist der Feldinhalt egal
Bei 6 Spalten sind also 64 Kombinationen möglich.
Im Kopf steht i.d.R wieviel Sätze betroffen sind.
Im Subfile wird Seitenweise mit Fetch eingelesen.
NULL Werte haben wir nicht.
Also bilden wir 2 sql, eins mit Count(*), das wir dank eurer Hilfe jetzt lösen konnten und eins
das wir entsprechend der gewählten Selektion bilden und mittels declare / Prepare / Fetch lesen.

Leider haben wir die Lösung von Herrn Fuerchau nicht verstanden.
Lt. Entwicker hat er verschiedenes getestet (2 Werte) aber nicht das ergebnis bekommen.

Vielen Dank
Dietlinde Beck