SQL Befehl treibt mich in den Wahnsinn.

[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.

[Starocotes]

Ich habe bei einer Division durch 0 noch nie einen SQL Fehler bekommen, weder über den Client noch beim STRSQL noch in PHP Skripts über ODBC.

Ich hatte noch ein paar mehr Probleme in meinem SQL da der die Zahlen falsch gecastet hat.

select
    asidn,
    asbz1,
    asbz2,
    coalesce(stlj, 0.00) as stlj,
    coalesce(stvj, 0.00) as stvj,
    coalesce(stvvj, 0.00) as stvvj,
    coalesce(frei, 0.00) as stfrei,
    cast(coalesce(stlj, 0) as float) / asumf2 as qmlj,
    cast(coalesce(stvj, 0) as float) / asumf2 as qmvj,
    cast(coalesce(stvvj, 0) as float) / asumf2 as qmvvj,
    cast(frei as float) / asumf2 as qmfrei,
    (
        cast(coalesce(stlj,0) as float) + cast(coalesce(stvj,0) as float) + cast(coalesce(stvvj,0) as float)
    ) / 34 as stpromon,
    (
        cast(coalesce(stlj,0) as float) + cast(coalesce(stvj,0) as float) + cast(coalesce(stvvj,0) as float)
    ) / 34 / asumf2 as qmpromon,
    case when 
        coalesce(stlj, 0) = 0 then 0 
    else 
        cast(frei as float) / (cast(stlj as float) / 10)
    end as rwlj,
    case when 
        coalesce(stlj, 0.00) = 0 then 0 
    else 
        cast(frei as float) / ((cast(coalesce(stlj, 0) as float) + cast(coalesce(stvj, 0) as float) + cast(coalesce(stvvj, 0) as float)) / ".$totmon.") 
    end as rw,
    (
        (
            cast(coalesce(stlj, 0) as float) + cast(coalesce(stvj, 0) as float) + cast(coalesce(stvvj, 0) as float)
        ) / 34
    ) * 9 - cast(frei as float) as stprod9,    
    (
        (
            (
                (
                    cast(coalesce(stlj, 0) as float) + cast(coalesce(stvj, 0) as float) + cast(coalesce(stvvj, 0) as float)
                ) / 34
            ) * 9 - cast(frei as float)
        ) / asumf2
    ) 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
    case when asme2 = 'st' then    
        ((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0) 
    else
        (((coalesce(stlj,0) + coalesce(stvj,0) + coalesce(stvvj,0)) / 34) * 9 - coalesce(frei,0)) / asumf2
    end desc

[Andreas_Prouza]

Ich habe bei einer Division durch 0 noch nie einen SQL Fehler bekommen, weder über den Client noch beim STRSQL noch in PHP Skripts über ODBC.

Dann versuche mal mit dem Ergebnis durch 0 weiter zu arbeiten.
Im STRSQL bekommst du da +++++++ als Ergebnis. Wenn du den Debug-Mode einschaltest, siehst du im Joblog:
CPF5035
Nachricht . . . : Datenzuordnungsfehler in Teildatei QSQPTABL.
Ursache . . . . : Bei Feld 10/0 im Satz mit Nummer 0 und Format *FIRST in
Teildatei QSQPTABL mit Nummer 1 der Datei QSQPTABL in Bibliothek QSYS2 ist
wegen Fehlercode 9 ein Datenzuordnungsfehler aufgetreten. Fehlercodes und
ihre Bedeutung:
9 - Division durch Null.

Wie PHP, Java oder sonstige Clients das Ergebnis interpretieren, ist eine andere Sache.
Mein Java SQL Client, liefert als Ergebnis zurück.
Beim ACS erhalte ich als Ergebnis null und eine Warnung:
SQL State: 01564
Vendor Code: 802

Wenn du es mit nullif machst, so wie Baldur beschrieben hat, funktioniert es.

[Starocotes]

Dann versuche mal mit dem Ergebnis durch 0 weiter zu arbeiten.

Das kann ich genauso gut oder schlecht wie mit jedem anderen NULLwert. Ob ich nun eine Division durch 0 mache oder ein Feld anspreche bei dem er den zugehörigen Satz in einem Join nicht gefunden hat ist nur sekundär von Interesse.

[Andreas_Prouza]

Das macht sehr wohl einen großen Unterschied
Ein COALESCE(dein-Null-Value, 0) + 1 geht.
Ein 100/0 + 1 geht eben nicht.

[Fuerchau]

Beachte die oben bereits mehrfach erwähnte Bedingung:

Ist bei einem Ausdruck ein Teilergebnis NULL dann ist der gesamte Ausdruck immer NULL!

D.h., mit NULL kann ich eben nicht weiterrechnen und genau dafür ist der Coalesce eben da.
Da er mehrere Ausdrücke erlaubt ist es in SQL eben sehr einfach einen möglichen Wert aus mehreren Joins zu ziehen:

select coalesce(a.value, b.value, c.value)
from mytable m
left join table1 a on a.key = m.key
left join table2 b on b.key = m.key
left join table3 a on c.key = m.key

Desweiteren:
STRSQL zeigt fehlerhafte Ausdrücke mit "+++++" an.
Im embedded SQL hört der SQL i.d.R. beim ersten Fehler auf.

Wenn ich per ODBC Daten abhole kann es sogar passieren, dass der SQL durch den Fehler vorzeitig aufhört, allerdings auch keine Fehlermeldung abgibt und ich somit gar nciht weiß, ob der SQL alles geliefert hat.
Alles schon erlebt.