Hi all,
There is an issue I can't seem to resolve. When I do a left join certain lines duplicate.The second gets empty fields except for the table keys (sipNo ma_kod desen varyant) . I think it's caused by the auto joining of tables but I am not sure.
I believe the second left join(in red) where I join siparis table to irsaliye table is the causing the problem. When I comment out the irsKesilmemis(in green) from sipstok table it seems to work fine. Please help!!
Here is my code:
ODBC CONNECT32 TO RAPOR;
siparis:
LOAD *;
SQL SELECT ol_or_no1, ol_or_no2 AS sipNo, ol_sr AS sira, ol_ma_kod AS ma_kod, ol_qty, ol_sqty_mt, ol_kapa, ol_ds_kod AS desen, ol_vry AS varyant, ol_plan_onay, date(ol_wrk_basdt), date(ol_onaydt),date(ol_terdt),
ol_or_anhno, ol_onay2, date(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 (date(ol_onaydt) BETWEEN '$(basTarih)' AND '$(bitisTarih)')*/
order by sipNo, sira, ma_kod, desen, varyant
;
irsaliye:
LOAD *;
SQL SELECT a.sh_ma_kod AS ma_kod, a.sh_ds_kod AS desen, a.sh_vry AS varyant, a.sh_of_or_no2 AS sipNo, a.sh_of_ol_sr AS sira,
sum(CASE WHEN sh_sf_hk_no = 30 AND sf_ir_no = 0 THEN a.sh_qty_mt ELSE 0 END) as irsKesilmemis
FROM DBA.stkhar AS a
LEFT JOIN DBA.stkfis AS b
ON a.sh_sf_anhno = b.sf_anhno
WHERE a.sh_sf_no1 = 2012 AND sh_sf_hk_no = 30 AND sf_ir_no = 0
group by sipNO, sira, 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_sf_no1 = 2012
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;
LEFT JOIN (siparis) LOAD * resident irsaliye;
sipstok:
LOAD
sipNo,sira, ma_kod, desen, varyant, ol_qty, standartStok,ol_sqty_mt, irsKesilmemis,
if(rowno()=1,
standartStok,
if(ma_kod = previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,
Peek(ATP)- Previous(ol_qty), standartStok)
) as ATP,
if(
if(ma_kod=previous(ma_kod) AND desen = previous(desen) and varyant = previous(varyant) ,
Peek(ATP)-Previous(ol_qty), standartStok)>= (ol_qty - irsKesilmemis), now(),
) AS planTarih
resident siparis;
store sipstok into planlama1.qvd (qvd);
Here is a sample from the resulting table: look at lines where sıra = 10 or 11
| sipNo | sira | ma_kod | desen | varyant | standartStok | ATP | irsKesilmemis | ol_qty | ol_sqty_mt | planTarih |
| 805 | 1 | SK3022-08 | | | 1350 | 1350,000 | 35010 | 35010 | 0 | 17.10.2012 12:20:57 |
| 889 | 2 | ME1005-01 | 101 | 9 | 580 | 580,000 | 1000 | 1000 | 0 | 17.10.2012 12:20:57 |
| 889 | 4 | ME1005-01 | 108 | 1 | 0 | | | 1000 | 0 | |
| 889 | 4 | ME1005-01 | 108 | 1 | | | | | | |
| 889 | 6 | ME1005-01 | 108 | 11 | 120 | 120,000 | 200 | 1000 | 200 | |
| 889 | 9 | ME1005-01 | 110 | 4 | 1240 | 1240,000 | 1000 | 1000 | 0 | 17.10.2012 12:20:57 |
| 889 | 10 | ME1005-01 | 111 | 1 | 120 | | | 1000 | 0 | |
| 889 | 10 | ME1005-01 | 111 | 1 | | | | | | |
| 889 | 11 | ME1005-01 | 113 | 1 | 4320 | | | 1000 | 0 | |
| 889 | 11 | ME1005-01 | 113 | 1 | | | | | | |
| 889 | 12 | ME1005-01 | 113 | 4 | 4880 | 4880,000 | 1000 | 1000 | 0 | 17.10.2012 12:20:57 |
| 889 | 13 | ME1005-01 | 634 | 2 | 840 | 840,000 | 1000 | 1000 | 0 | 17.10.2012 12:20:57 |
| 889 | 14 | ME1005-01 | 634 | 5 | 1680 | 1680,000 | 1000 | 1000 | 0 | 17.10.2012 12:20:57 |
Thanks a lot
