Hm gitu ya, kalau liat datanya sih mau ngga mau harus pake sub query. Saran saya kenapa bukan applikasinya saja yg di ubah, misalkan qty harus di isi atau jika null (dengan asumsi tidak ada perubahan qty) tidak perlu dimasukan ke table. but anyway ini mungkin bisa dipakai.
[EMAIL PROTECTED]> select y.tgl, y.qty, 2 (select max(x.qty) 3 from t2 x 4 where 5 x.tgl = 6 ( 7 select max(z.tgl) 8 from t2 z 9 where z.tgl<=y.tgl 10 and z.qty is not null 11 ) 12 ) qty2 13 from t2 y; TGL QTY QTY2 --------- ---------- ---------- 01-JAN-05 1 1 02-JAN-05 1 03-JAN-05 1 04-JAN-05 4 4 05-JAN-05 4 06-JAN-05 7 7 07-JAN-05 9 9 08-JAN-05 5 5 09-JAN-05 5 10-JAN-05 5 11-JAN-05 5 11 rows selected. Cheers ----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[email protected]> Sent: Monday, August 01, 2005 15:06 PM Subject: Re: [indo-oracle] Help query > Sorry penjelasannya masih kurang, quantitynya ga selalu semakin besar. > Bisa juga jadi kecil. Kalo pake max kan selalu ngambil yang terbesar. > Contohnya aga diubah dikit: > > TGL QTY QTY_A > --------- ---------- ------------------------------- > 01-JAN-05 1 1 > 02-JAN-05 1 > 03-JAN-05 1 > 04-JAN-05 4 4 > 05-JAN-05 4 > 06-JAN-05 7 7 > 07-JAN-05 5 5 > 08-JAN-05 5 > 09-JAN-05 5 > > > Thanks > > >> drop table t2; >> create table t2 (tgl date, qty number); >> >> insert into t2 values (to_date('01-01-2005','DD-MM-YYYY'),1 ); >> insert into t2 values (to_date('02-01-2005','DD-MM-YYYY'),NULL ); >> insert into t2 values (to_date('03-01-2005','DD-MM-YYYY'),NULL ); >> insert into t2 values (to_date('04-01-2005','DD-MM-YYYY'),4 ); >> insert into t2 values (to_date('05-01-2005','DD-MM-YYYY'),NULL ); >> insert into t2 values (to_date('06-01-2005','DD-MM-YYYY'),7 ); >> insert into t2 values (to_date('07-01-2005','DD-MM-YYYY'),9 ); >> insert into t2 values (to_date('08-01-2005','DD-MM-YYYY'),NULL ); >> insert into t2 values (to_date('09-01-2005','DD-MM-YYYY'),NULL ); >> >> select tgl, qty, max(nvl(qty,0)) over (order by tgl) from t2; >> >> [EMAIL PROTECTED]> select tgl, qty, max(nvl(qty,0)) over (order by tgl) from >> t2; >> >> TGL QTY MAX(NVL(QTY,0))OVER(ORDERBYTGL) >> --------- ---------- ------------------------------- >> 01-JAN-05 1 1 >> 02-JAN-05 1 >> 03-JAN-05 1 >> 04-JAN-05 4 4 >> 05-JAN-05 4 >> 06-JAN-05 7 7 >> 07-JAN-05 9 9 >> 08-JAN-05 9 >> 09-JAN-05 9 >> >> 9 rows selected. >> >> >> Cheers, >> >> >> ----- Original Message ----- >> From: "Doni Hidayat" <[EMAIL PROTECTED]> >> To: <[email protected]> >> Sent: Monday, August 01, 2005 13:43 PM >> Subject: [indo-oracle] Help query >> >> >> Tolongin dong, >> Saya punya data sebagai berikut, >> Table A: >> tanggal quantity >> ------------------- >> 1-1-05 1 >> 2-1-05 >> 3-1-05 >> 4-1-05 4 >> 5-1-05 >> 6-1-05 7 >> 7-1-05 9 >> 8-1-05 >> 9-1-05 >> >> Saya ingin kan data sebelumnya jika terisi null >> >> tanggal quantity quantity_A >> -------------------------------- >> 1-1-05 1 1 >> 2-1-05 1 >> 3-1-05 1 >> 4-1-05 4 4 >> 5-1-05 4 >> 6-1-05 7 7 >> 7-1-05 9 9 >> 8-1-05 9 >> 9-1-05 9 >> >> Ada yang bisa bantu querynya >> >> Thanks >> >> >> >> >> -- >> -----------I.N.D.O - O.R.A.C.L.E--------------- >> Keluar: [EMAIL PROTECTED] >> Website: http://indo-oracle.lizt.org (NEW) >> ----------------------------------------------- >> >> Bergabung dengan Indonesia Thin Client User Groups, >> Terminal Server, Citrix, New Moon Caneveral, di: >> http://indo-thin.vze.com >> Yahoo! Groups Links >> >> >> >> >> >> >> >> >> >> >> ___________________________________________________________ >> Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with >> voicemail http://uk.messenger.yahoo.com >> >> >> >> -- >> -----------I.N.D.O - O.R.A.C.L.E--------------- >> Keluar: [EMAIL PROTECTED] >> Website: http://indo-oracle.lizt.org (NEW) >> ----------------------------------------------- >> >> Bergabung dengan Indonesia Thin Client User Groups, >> Terminal Server, Citrix, New Moon Caneveral, di: >> http://indo-thin.vze.com >> Yahoo! Groups Links >> >> >> >> >> >> >> > > > > > -- > -----------I.N.D.O - O.R.A.C.L.E--------------- > Keluar: [EMAIL PROTECTED] > Website: http://indo-oracle.lizt.org (NEW) > ----------------------------------------------- > > Bergabung dengan Indonesia Thin Client User Groups, > Terminal Server, Citrix, New Moon Caneveral, di: > http://indo-thin.vze.com > Yahoo! Groups Links > > > > > > ___________________________________________________________ To help you stay safe and secure online, we've developed the all new Yahoo! Security Centre. http://uk.security.yahoo.com ------------------------ Yahoo! Groups Sponsor --------------------~--> <font face=arial size=-1><a href="http://us.ard.yahoo.com/SIG=12h1flph0/M=362329.6886308.7839368.1510227/D=groups/S=1705007183:TM/Y=YAHOO/EXP=1122985662/A=2894321/R=0/SIG=11dvsfulr/*http://youthnoise.com/page.php?page_id=1992 ">Fair play? Video games influencing politics. Click and talk back!</a>.</font> --------------------------------------------------------------------~-> -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [EMAIL PROTECTED] Website: http://indo-oracle.lizt.org (NEW) ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.vze.com Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
