SQL Befehl treibt mich in den Wahnsinn.

[Starocotes]

Ich bin davon ausgegangen das ich mit einem coalesce(((frei / stlj) / 10), 0) as rwlj sehr effektiv dafür sorgen kann das rwlj in keinem Fall einen NULLwert enthält.

Pustekuchen. In einigen wenigen Sätzen liefert mit mein SQL trotzdem einen NULLwert zurück und das ist seltsamerweise nicht davon abhängig ob frei oder stlj NULLwerte sind.

Jemand ansatzweise ne Idee woran sowas liegen kann?



select asidn,
asbz1,
asbz2,
coalesce(stlj,0) as stlj,
coalesce(stvj,0) as stvj,
coalesce(stvvj,0) as stvvj,
coalesce(frei,0) as stfrei,
coalesce(stlj / asumf2,0) as qmlj,
coalesce(stvj / asumf2,0) as qmvj,
coalesce(stvvj / asumf2,0) as qmvvj,
coalesce(frei / asumf2,0) as qmfrei,
coalesce((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34, 0) as stpromon,
coalesce((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34 / asumf2, 0) as qmpromon,
coalesce(((frei / stlj) / 10), 0) as rwlj,
coalesce(coalesce(frei,0) / ((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0))) / 34,0) as rw,
coalesce((((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0)),0) as stprod9,
coalesce((((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0)) / asumf2,0) as qmprod9,
asabc,
asme2
from
stdprod.afassp
left join
(
select
s3idn,
sum(s3tlmg) as stlj
from
stdprod.afs3sp
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z') and
s3rgd <= '20201031'
group by
s3idn
) ulj
on
ulj.s3idn = asidn
left join
(
select
s3idn,
sum(s3tlmg) as stvj
from
wwdta.afs3spvj
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z')
group by
s3idn
) uvj
on
uvj.s3idn = asidn
left join
(
select
s3idn,
sum(s3tlmg) as stvvj
from
wwdta.afs3spvvj
where
substr(s3afa, 1, 1) not in ('M', 'Y', 'Z')
group by
s3idn
) uvvj
on
uvvj.s3idn = asidn
left join
(
select
abidn,
sum(abbst) as frei
from
stdprod.afabsp
where
substr(ablag, 3, 2) < '50'
group by
abidn
) bst
on
abidn = asidn
where
substr(asidn, 11, 1) = '1' and
substr(asidn, 13, 2) < '40' and
(
coalesce(stlj,0) <> 0 or
coalesce(stvj,0) <> 0 or
coalesce(stvvj,0) <> 0 or
coalesce(frei,0) <> 0
)
order by
((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0) desc

[B.Hauser]

Wenn man mit NULL-Werten arbeitet, muss man wissen, dass wenn man zu einem NULL-Wert etwas hinzufügt oder abzieht oder einen NULL-Wert in einer Formel hat, dass das Ergebnis wieder NULL ist.

Wenn Du also eine Division machen möchtest, und beide Werte NULL sein können, musst Du beide Werte (einzeln mit COALESCE) in einen Default-Wert konvertieren.
Den äußeren COALESCE kannst Du Dir dann jedoch sparen, da ja alle Werte in einen Default-Wert konvertiert worden sind.

So sind nun mal die Regeln.

Ich würde an dieser Stelle zunächst eine CTE (Common Table Expression) machen, in der die Tabellen verjoint werden und dann die eventuellen NULL Werte konvertiert werden.
Auf der Basis dieser CTE würde ich dann alle anderen Aktionen durchführen.
Wenn Du diese CTE mehrfach verwenden kannst/musst, würde sich anstatt einer CTE eine (permanente) View anbieten.

Birgitta

[Starocotes]

Also so:

coalesce(frei,0) / coalesce(stlj,0) / 10 as rwlj,


das macht aber keinen Sinn, weil ich dann in jedem Fall einen NULL Wert bekomme wenn stlj tatsächlich 0 ist.

Das hier funktioniert besser:

case when coalesce(stlj, 0) = 0 then 0 else coalesce(((frei / stlj) / 10), 0) end as rwlj,




Aber sollte doch eigentlich nicht nötig sein, oder?

[B.Hauser]

Es gibt natürlich viele Wege nach Rom!
Das hier geht auch:

Case When Coalesce(frei, 0) = 0 or Coalesce(stlj, 0) = 0 Then 0 Else frei/Stlj / 10 End

Birgitta

[Fuerchau]

Warum kompliziert wenn es auch einfach geht?
Wenn man mehrere Werte in einer Formel verarzten will muss man ausschließen dass einer davon NULL ist. Desweiteren muss man verhindern, eine Division durch Null zu erhalten, was den ganzen SQL abbricht.

coalesce(W1 / nullif(W2, 0), 0)

ist die sichere und kürzeste Variante.

Für dein Bespiel wäre das:
coalesce(frei/ nullif(Stlj, 0) / 10, 0)

Somit kann man diesen Ausdruck auch kombinieren:

coalesce(W1 / nullif(W2, 0), 0) + coalesce(W2 / nullif(W3, 0), 0)

Ich habe nun nicht alle deine Coalesce geprüft, aber irgendwo hast du einen vergessen.

[Andreas_Prouza]

Das Coalesce oder ifnull sollte auf jeden fall immer NULL in einen Defaultwert umwandeln.
Ich kann mir nur vorstellen, dass es hier wegen dem LEFT JOIN eine Konstellation gibt, wo das nicht zutrifft. Müsste man sich im Detail anschauen.
Du könntest (z.B. via CTE, sprich: with x as) das ganze als Subselect hinterlegen.
Dann machst du ein "Select ... from x" wo du im Select das Zeug mit COALESCE & Co machst.
Dadurch ersparst du dir dann das CASE & Co.

lg Andreas

[Andreas_Prouza]

Und grundsätzlich: Division durch 0???
Im ernst jetzt?? :-D

[Fuerchau]

Ja klar. Division durch 0 führt zum Fehler, ein "W1 / nullif(w2, 0)" liefert NULL, da der Nullif NULL liefert, wenn W2 = 0 ist.
Die NULL-Bedingung lautet ja: ist ein Operand NULL ist das Ergebnis NULL.

Wenn ich eine Umsatz-Auswertung mache und den Durchschnittspreis ermitteln möchte rechne ich ja "avg(Wert / Menge)".
Nun gibt es aber Auftragsarten, in den die Menge 0 sein kann, z.B. Frachtkosten.
Somit würde ich bei "Wert / Menge" einen Fehler bekommen.

Nullif ist die Kurzform von "case when Menge <> 0 then menge end".
oder ganz genau: case Menge when 0 then NULL else Menge end".

Mittels nullif(w1, w2) lassen sich viele Varianten ausdenken.

[Andreas_Prouza]

Sorry, aber du produzierst damit einen Fehler (=Exception)!
Es gibt sogar einen Job-Log Eintrag dafür.
Hast du das Beispiel mal im STRSQL ausprobiert?
Dort kannst du auch nicht mit einem COALESCE & Co das NULL zu einem 0 ändern, da es ein Fehler ist!
Und ich denke dass auch hier der eigentliche der Grund an der Division durch 0 zu finden ist.

[Fuerchau]

Dann gehen diese Fehler seit Jahren an mir vorbei;-).

values ( 12 / nullif(0, 0) )

der Ausdruck liefert korrekt NULL ohne Fehler

values ( coalesce(12 / nullif(0, 0) , 0) )

dieser Ausdruck liefert korrekt 0 ohne Fehler.
D.h.: Eine Division durch 0 kann ichmit NULLIF(...) zu NULL entschärfen.