[NEWSboard IBMi Forum]
Seite 1 von 2 1 2 Letzte
  1. #1
    Registriert seit
    Jun 2005
    Beiträge
    98

    SQL Befehl treibt mich in den Wahnsinn.

    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?


    Code:
        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

  2. #2
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    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
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  3. #3
    Registriert seit
    Jun 2005
    Beiträge
    98
    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?

  4. #4
    Registriert seit
    Aug 2001
    Beiträge
    2.869
    Es gibt natürlich viele Wege nach Rom!
    Das hier geht auch:
    Code:
    Case When Coalesce(frei, 0) = 0 or Coalesce(stlj, 0) = 0 Then 0 Else frei/Stlj / 10 End
    Birgitta
    Birgitta Hauser

    Anwendungsmodernisierung, Beratung, Schulungen, Programmierung im Bereich RPG, SQL und Datenbank
    IBM Champion seit 2020 - 4. Jahr in Folge
    Birgitta Hauser - Modernization - Education - Consulting on IBM i

  5. #5
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  6. #6
    Registriert seit
    Nov 2020
    Beiträge
    315
    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

  7. #7
    Registriert seit
    Nov 2020
    Beiträge
    315

  8. #8
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  9. #9
    Registriert seit
    Nov 2020
    Beiträge
    315
    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.

  10. #10
    Registriert seit
    Feb 2001
    Beiträge
    20.207
    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.
    Dienstleistungen? Die gibt es hier: http://www.fuerchau.de
    Das Excel-AddIn: https://www.ftsolutions.de/index.php/downloads
    BI? Da war doch noch was: http://www.ftsolutions.de

  11. #11
    Registriert seit
    Jun 2005
    Beiträge
    98
    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.

    Code:
    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

  12. #12
    Registriert seit
    Nov 2020
    Beiträge
    315
    Zitat Zitat von Starocotes Beitrag anzeigen
    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.

Similar Threads

  1. Muss mich hier heute ständig neu anmelden !!!
    By Peet in forum IBM i Hauptforum
    Antworten: 1
    Letzter Beitrag: 16-04-20, 15:18
  2. XML-INTO - ich glaub mich tritt ein Pferd
    By camouflage in forum IBM i Hauptforum
    Antworten: 14
    Letzter Beitrag: 18-02-20, 18:37
  3. Befehl CPYTOIMPF
    By alex61 in forum IBM i Hauptforum
    Antworten: 21
    Letzter Beitrag: 09-09-19, 19:55
  4. Antworten: 8
    Letzter Beitrag: 18-05-16, 15:11
  5. Antworten: 5
    Letzter Beitrag: 11-12-13, 03:27

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • You may not post attachments
  • You may not edit your posts
  •