SQL-Abfrage mit Sub-Sub-Select ;)

[Fuerchau]

Die Zwischenrechung mit Double() ist in der Tatsache begründet, dass SQL die Zwischenfelder zu groß macht (> 31 Stellen) und den Rechenvorgang dann abbricht. Auch bei Dec64 steigt SQL da aus.
Mit Dec(Round ()) bekommt man das dann wieder hin.

[Fuerchau]

Und für alle, die wissen wollen was die IBM i noch so alles kann:
In einem ERP laufen Verbrauchszahlen aus dem Ruder, da unklar ist wer wann wo was genau bucht. Daher musste ich eine Korrektur bauen, die mit SQL natürlich erheblich einfacher war.
Die Zieltabelle enthält verdichtete Verbrauchszahlen auf JJJJMM-Basis je Artikel.
Die Verbräuche werden durch Lagerbewegungen bestimmt, die in 2 Tabellen vorkommen.
Der MERGE erlaubt dann die Korrektur auf dem Ziel. CTE's sind bei dieser Form nicht erlaubt, jedoch beliebige derived Tables, sowohl scalar also joined.

Hier noch mal zur Erklärung, dass ein "Order by" mit "Limit 1" große Vorteile bietet, da komplexe Bedingungen um genau 1 Ergebnis zu erhalten nun obsolet sind.

    if IndexChecked = *off;
        IndexChecked = *on;
        // nur zur Sicherheit, falls der Index entfernt wurde
        exec sql create index FILELIB.D060IV
                     on D060 (PLORT, PLDAT, PLMENG, PLVGA, PLPGM);
    endif;
    exec sql
        merge into D220 to
          using (


           select *
             from (
                select plort            ARORT
                      ,plnrkz           ARKZ
                      ,plnr             ARNR
                      ,plhalb           ARHALB
                      ,year(plstda) * 100 + month(plstda) ARJJMM
                      ,sum(0-plmeng)    arvb


                  from (


                   select plort
                         ,plnrkz
                         ,plnr
                         ,plhalb
                         ,plmeng
                         ,pldat
                         ,plvga
                         ,plgrau
                         ,plpgm


                         ,case when plvga <> 500 then pldat
                               else (select streda
                                       from D201
                                      where stpnlg = v.plort
                                        and streda >= v.pldat
                                        and staunr = v.plgrau
                                      order by  stpnlg
                                               ,stjjjj
                                               ,stsa
                                               ,stnr
                                               ,staunr
                                               ,streda
                                       limit 1
                                     )
                           end     plstda


                    from (
                         select plort
                              , plnrkz
                              , plnr
                              , case plhalb
                                when '1' then '1'
                                else '2'
                                end                              as plhalb
                              ,pldat
                              ,plmeng
                              ,plvga
                              ,plgrau
                              ,plfanr
                              ,plpgm


                          from D060 a
                        where a.PLDAT >= first_day( current date - 1 month)  // 1. des Vormonats
                          and a.PLORT  = :pKonzerngesellschaft
                          and a.PLNRKZ = :pArtikelkennzeichen
                          and a.PLNR   = :pArtikelnummer
                          and a.PLMENG < 0.00
                          and a.PLVGA in (100, 110, 140, 500, 610)           // VGA Verbräuche
                          and a.PLPGM not in ('FAKOMMT01R', 'BOPFEAU01R', 'BOPA213', 'BOPA213TAB')


                        union all


                        select plort
                              , plnrkz
                              , plnr
                              , case plhalb
                                when '1' then '1'
                                else '2'
                                end                              as plhalb
                              ,pldat
                              ,plmeng
                              ,plvga
                              ,plgrau
                              ,plfanr
                              ,plpgm


                          from D062 a
                        where a.PLDAT >= first_day( current date - 1 month)  // 1. des Vormonats
                          and a.PLORT  = :pKonzerngesellschaft
                          and a.PLNRKZ = :pArtikelkennzeichen
                          and a.PLNR   = :pArtikelnummer
                          and a.PLMENG < 0.00
                          and a.PLVGA in (100, 110, 140, 500, 610)           // VGA-Verbräuche
                          and a.PLPGM not in ('FAKOMMT01R', 'BOPFEAU01R', 'BOPA213', 'BOPA213TAB')


                        ) v
                  )
                 where not plstda is null        -- noch nicht in Statistik
                 group by plort
                         ,plnrkz
                         ,plnr
                         ,plhalb
                         ,year(plstda) * 100 + month(plstda)
           )


        ) fr


        on fr.arort  = to.arort
       and fr.arhalb = to.arhalb
       and fr.arkz   = to.arkz
       and fr.arnr   = to.arnr
       and fr.arjjmm = to.arjjmm
       and fr.arvb  <> to.arvb


       when matched then
            update set to.arvb = fr.arvb
    ;