4 Replies Latest reply: Oct 8, 2012 8:53 AM by bhancerli RSS

    Duplication of lines while updating field after a join..

      Hı all,

       

      I am in desperate need of help with the code below. I am loading two tables from ODBC, then joining them and updating the value of a field based on certain conditions.

       

      up until the sipstok: label I don't have any problems. But the last load duplicates certain rows. I tried using the noconcatenate expression but it didn't help.

       

      Because of the duplication updateing the value of the standartstok field doesn't work properly. Any help is highly appreciated.

       

       

      Thanks.

       

       

       

      ODBC CONNECT32 TO RAPOR;

       

       

      siparis:

      SQL SELECT ol_or_no1, ol_or_no2, ol_sr, ol_ma_kod AS ma_kod, ol_qty, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, ol_wrk_basdt, ol_onaydt,

                                     ol_or_anhno, ol_onay2, ol_onay2dt,or_pe_no, or_anhno, or_rr_no

                FROM DBA.ordln

                JOIN DBA.ord

                ON ordln.ol_or_anhno = ord.or_anhno

                WHERE ol_kapa = 'A' AND ol_or_no1 = 2012 AND ol_ma_kod = 'ME1005-01'  /*AND ol_onay2 <> '          */

                order by ol_or_no2, ma_kod, desen, varyant

      ;

       

       

       

       

      stok:

      Load

           *,

           brutStok-standartStok as kMetrajStok;

       

       

      SQL SELECT  a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant,

                                    sum(CASE WHEN a.sh_sf_hk_no < 30 THEN a.sh_qty_mt ELSE -1*a.sh_qty_mt END) AS brutStok,

                                    sum(CASE WHEN (a.sh_sf_hk_no < 30 AND a.sh_qty_mt = b.ma_kg_top) THEN a.sh_qty_mt

                                                         WHEN (a.sh_sf_hk_no >= 30 AND a.sh_qty_mt = b.ma_kg_top) THEN -1*a.sh_qty_mt

                                                         ELSE 0*a.sh_qty_mt END)as standartStok

             FROM DBA.stkhar AS a

             LEFT JOIN DBA.mam AS b

             ON a.sh_ma_kod = b.ma_kod

             WHERE a.sh_ma_kod ='ME1005-01' AND a.sh_sf_no1 = 2012/* AND a.sh_ds_kod = '122'*/ AND a.sh_sf_ar_no = 20

             GROUP BY  a.sh_ma_kod, a.sh_ds_kod, a.sh_vry

             order by ma_kod, desen, varyant

      ;

       

       

      LEFT JOIN (siparis) LOAD * resident stok;

       

       

      sipstok:

      NoConcatenate

      LOAD

       

       

                                    ol_or_no2,ol_sr, ma_kod, desen, varyant, ol_qty,

                                              if(rowno()=1,

                                           standartStok,

                                                   if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                                Previous(standartStok)- Previous(ol_qty), standartStok)

                                                 ) as standartStok,

               

                                                        if(

                                                        if(ma_kod=previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,

                                                Previous(standartStok)-Previous(ol_qty), standartStok)>=ol_qty, now(),

                                                ) AS planTarih

                                               

                                                

      resident siparis;