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