create table data(tanggal varchar2(7),qty number(1));
insert into data values('1-1-05','1');
insert into data values('2-1-05','');
insert into data values('3-1-05','');
insert into data values('4-1-05','4');
insert into data values('5-1-05','');
insert into data values('6-1-05','7');
insert into data values('7-1-05','9');
insert into data values('8-1-05','');
insert into data values('9-1-05','');
berikut sqlnya, dimana bila null maka qty akan mengambil nilai
sebelumnya yg bukan null.
select b.tanggal,b.qty,
coalesce(b.qty,
(select a.qty from (select rownum id,tanggal,qty from data) a where
a.id=b.id-1),
(select a.qty from (select rownum id,tanggal,qty from data) a where
a.id=b.id-2),
(select a.qty from (select rownum id,tanggal,qty from data) a where a.id=b.id-3)
) qty_A
from
(select rownum id,tanggal,qty from data) b
note for the lines: select a.qty from ..... a.s.o ini dg asumsi jarak
antara satu qty yg bukan null ke qty
berikutnya yg bukan null maksimal ada 3 data yang kosong / null.
so ,bila ternyata anda menemukan lebih dari 3 data null , pls add more
lines (the "select a.qty from ..").
may be it's look stupid , but for a short term it's really help you.
limitation: don't try this sql for a batch update ... b'coz you're
gonna die ,,...:D (cmiiw)
ok , may be there's a better way out to acchieve what you really
wanted. this just a first aid :))
On 5/1/05, Doni Hidayat <[EMAIL PROTECTED]> wrote:
> 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! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12hqrmk61/M=362329.6886308.7839368.1510227/D=groups/S=1705007183:TM/Y=YAHOO/EXP=1123157397/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/
