SQL set oder select into mit dynamischer Selektion

[BenderD]

NULL Werte haben wir nicht.
Dietlinde Beck

... verwendet ihr keine joins?

Noch eine Anmerkung zu dem count: wenn während der Ausführung Sätze eingefügt, oder gelöscht werden, dann stimmt der count nicht mit dem Resultset überein.

D*B

[dschroeder]

Ich habe die Lösung von Baldur zunächst auch nicht verstanden. Aber ich habe mal die KI gefragt. Die konnte es mir erklären.

Mein bisheriges Wissen war, dass man Null-Indikatoren beim Fetch angeben kann, damit das System darin mitteilen kann, ob ein gelesenes Feld NULL ist.

Was ich nicht wusste ist, dass es umgekehrt auch geht. Ich kann hinter eine Hostvariable (z.B. im where) einen Null-Indikator schreiben, um dem System damit zu sagen, dass die Hostvariable den Wert NULL hat. Das ist notwendig, wenn die Hostvariable selber nicht NULL-fähig ist.

Ich hoffe, ich habe es richtig verstanden und korrekt beschrieben.

[Fuerchau]

Das hast du nun korrekt verstanden;-).
Das geht nicht nur beim Where sondern auch beim Insert oder Update.
Im Gegensatz zu Dieter bin ich überzeugt, dass NULL-Werte durchaus Sinn machen.
Bei Aggregaten werden NULL's z.B. ignoriert und belasten vor allem Min/Max/Avg/STDV o.ä. eben nicht.
Ich kann aber 0 nicht generell ausschließen.
NULL heißt eben "nicht existent", so nach dem Motto beim Auswählen: Ja, Nein, Weiß nicht.

[BenderD]

Ich habe die Lösung von Baldur zunächst auch nicht verstanden. Aber ich habe mal die KI gefragt. Die konnte es mir erklären.

Mein bisheriges Wissen war, dass man Null-Indikatoren beim Fetch angeben kann, damit das System darin mitteilen kann, ob ein gelesenes Feld NULL ist.

Was ich nicht wusste ist, dass es umgekehrt auch geht. Ich kann hinter eine Hostvariable (z.B. im where) einen Null-Indikator schreiben, um dem System damit zu sagen, dass die Hostvariable den Wert NULL hat. Das ist notwendig, wenn die Hostvariable selber nicht NULL-fähig ist.

Ich hoffe, ich habe es richtig verstanden und korrekt beschrieben.

Das wird gebraucht, um NULL values zu erzeugen.
Nehmen wir mal ein kleines, typisches Beispiel:
- im Auftragskopf steht eine Kundennummer
- für dieses Feld erlaube ich Null values
- dann lege ich eine referential constraint drauf, die schützt, dass da keine Kundennummern reinpassen, die es nicht gibt.
- füge ich einen Barverkauf ein, dann hat der keinen Kunden
- dafür brauche ich dann in manchen Programmiersprachen (RPG) eine Krücke, da null reinzuschreiben.
et voila!

Beim Vergleich (where clause) ist "where Feld = null" nicht erlaubt, das muss dann "where feld is null" heißen.
Wenn der Compiler oder die runtime das anders sieht und das nicht merkt, dann ist das nicht "schön" sondern "doof".
Wenn das jemand trotzdem benutzt, dann ist das nicht "clever", sondern "blöd", weil das nicht mehr funzt, wenn der Compiler und/oder die runtime den Fehler beheben. Dann ist auch beschweren, dass das früher doch ging, zwar einen Versuch wert, bringt aber nix.

D*B

[BenderD]

Das hast du nun korrekt verstanden;-).
Das geht nicht nur beim Where sondern auch beim Insert oder Update.
Im Gegensatz zu Dieter bin ich überzeugt, dass NULL-Werte durchaus Sinn machen.
Bei Aggregaten werden NULL's z.B. ignoriert und belasten vor allem Min/Max/Avg/STDV o.ä. eben nicht.
Ich kann aber 0 nicht generell ausschließen.
NULL heißt eben "nicht existent", so nach dem Motto beim Auswählen: Ja, Nein, Weiß nicht.

Lieber Baldur,

ich bin seit (gefühlt) 1870 Verfechter von Null values und referential constraints und habe das in zahlreichen SQL Kursen gepredigt und Ignoranten ausgeschiimpft (in vielen Beiträgen in Foren zu finden).

Schönen Tag noch

Dieter

[Fuerchau]

Ich habe noch nicht erlebt, dass man NULL anders als "is Null" gezielt abfragen kann.
Allerdings hilft dies eben ungemein mit NULL gezielt zu vergleichen, wenn man das als negatives Ergebnis wünscht.
Besonders wenn man dies dann auch noch mit Or-Klauseln verknüpft.

Beispiel left join vs. inner join:

select *
from filea a
inner join fileb b on ...
inner join filec c on ...
where b.Feld = 'X'
or c.Feld = 'Y'

Wir wissen nun, dass Inner join nur die Zeilen selektiert, die auf beiden Seiten vorhanden sind.
Somit liefert das Ergebnis nur die Zeilen, die in fileb und in filec vorhanden sind.

Wenn aber in fileb oder in filec Zeilen auch fehlen dürfen, muss ich halt left join verwenden.
select *
from filea a
left join fileb b on ...
left join filec c on ...
where b.Feld = 'X'
or c.Feld = 'Y'

Weiterhin gilt, dass b.Feld = 'X' sein muss. Wenn aber die b.Zeile fehlt, ist b.Feld dann NULL und somit wird b.Feld(Null) = 'X' negativ und die Or-Bedingung wird geprüft.

Lange Rede kurzer Sinn:
Ein Vergleich NULL mit Irgengwas ist legitim und kann mittles NULL-Anzeiger im RPG gezielt ebenso verwendet werden.

Wenn du eine Prozedur/Funktion schreibst und Variablen deklarierst, können diese eben auch NULL haben, wenn du NOT NULL nicht anwendest.

[BenderD]

... mein Denkfehler bezüglich Vergleiche mit NULL war, dass "is null" der einzige Vergleich ist, der true liefern kann. Ansonsten ist das Resultat immer false - womit man natürlich rumspielen kann. Wie auch immer: die between Variante mit coalesce für die Felder, die null werden könnten ist einfacher und lesbarer.

[Fuerchau]

Aber nicht unbedingt performanter, da das Feld auf jeden Fall abgefragt werden muss.
Frage ich zuerst den Parameter auf leer ab, wird der Or-Zweig nicht geprüft.
Wenn die Prüfung dann u.U. auch noch auf einen left Join geht, kann der Optimierer ggf. sogar auf den Zugriff verzichten.
Aber mit der Performance ist das so eine Sache.
Ich habe eine komplexe Preisfindung sqltechnisch optimert und somit vereinfacht (z.B. mit join lateral). Da der Zugriff aber keine Massendaten verarbeitet dauerte die alte Preisfindung ca. 100 Millissekunden und die neue liegt bei kleiner 5. Das wird dann erst bei einer Preisliste auffallen, wenn 10.000de Preise abgefragt werden.

[Fuerchau]

Final lässt sich noch folgendes sagen:
Ein count(*) vorher und anschließend enen normalen Select ist auch kontraproduktiv, da man 2x die Datenbank abfragen muss. Der count(*) dauert durchaus genauso lange wie der 2. SQL.

Mittels

select count(*) over() as Anzahl
, F.*
from MyFile F
where ....

erhält man beim 1. Fetch bereits die Anzahl Zeilen. Zusätzlich bleibt diese Anzahl dann auch stabil im Ergebnis. Für das befüllen kann man dann z.B. die ersten 2000 Zeilen laden.
Ein where "x between min and max" führt zu einem Tablescan und auch nicht zu einer Indexnutzung.

Und was die ganze Diskussion angeht und 0 oder Blank zulässige Bedingungen sind, so hilft auch hier der Null-Anzeiger:

where ( : P1 : Ind1 is null or Col1 = : P1)
and ( : P2 : Ind2 is null or Col2 = : P2)
usw.

Wenn laut obiger Aussage P1 und P2 beliebige Werte haben können aber ingnoriert werden sollen, so wird die Bedingung eben wahr, wenn Ind1 = -1 ist und somit P1 als NULL angenommen wird und P1 is null somit true ist.
Die Anzahl der Kombinationen ist mit den Nullanzeigern beliebig kombinierbar.

[BenderD]

- in einer Anzeige gibt es keine null values, die müssen eh mit coalesce raus!
- von der Performance macht man für sowas einen Blockfetch, der sagt einem auch gleich noch, wieviele es gelesen hat. Bei Maximalgröße zeigt man dann > 1000, wenn man denn in einen 1000er Block einliest. Dann stimmt der Wert auch und eine angezeigte Auswahlliste ist immer eh nur eine Momentaufnahme.

[ILEMax]

@Baldur Irgendwas fehlt mir ...

Subfile mit

PLZ   Vertreter    NR

1    v1        n1
1    v11       n3
1    v12       n1
2    v21       n1
2    v22       n24
3    v11       n1

Im CTRLSATZ kann ich filter setzen

Wenn ich PLZ auf 3 setze soll ein Satz kommen (ind2 und ind3 auf -1 setzen)
Wenn ich Vertreter auf v11 setze sollen 2 Sätze kommen (ind1 und ind3 auf -1 setzen)
Wenn ich Vertreter auf v11 setze und Nr auf n3 soll ein Satz kommen (ind1 auf -1 setzen)

Die variablen im CTRL-Satz heissen PLZ, VT und NR
Die variablen in der Datei heisseb D_PLZ, D_VT und D_NR

SQL für die 3 selektionen sieht so aus?

select count(*) over () as anzahl, D_plz, d_vt, d_nr 
  from datei 
where (:Plz :ind1 is null or :plz = d_plz)
   and (:VT :ind2 is null or :VT = D_VT)
   and (:NR :ind3 is null or :NR = D_NR)

Und wenn es 87.524 Sätze sind, die die gewählte kombination haben, steht das in (allen) gefetchten Sätzen in Anzahl?
Richtig?

[Fuerchau]

Ja, bei den sog. OLAP-Funktionen wird dies für jeden Satz ausgeworfen.
Dies hat den Vorteil, dass man auch Summenergebnisse verrechnen kann.
Z.B:

Menge * 100 / nullif(sum(Menge) over(), 0) AnteilMenge
Menge * 100 / nullif(sum(Menge) over(Partition by Auftrag), 0) "AnteilMenge je Auftrag"

u.v.m.

Der Vorteil des Count beim Subfile-Laden ist, man könnte dem User nach dem 1. Fetch die Zahl nennen und anbieten die ersten 2000 Zeilen zu laden ohne noch mal neu abfragen zu müssen.