dari atas ke bawah itu harus deterministic, artinya harus ada kolom yang digunakan sebagai acuan untuk dilakukan ordering. misal kalau mau diurutkan berdasarkan cover tinggal ganti aja dari
order by b.gid, a.premium desc menjadi order by b.gid, a.cover --- In [email protected], risti <[EMAIL PROTECTED]> wrote: > > Dear Rudi, > Thanks, scriptnya ok bngt... > utk id yang 1 sdh benar,sdang utk yang id =2, > > ID C PREMIUM > ------------ --- - ------------ --- > 2 A 1000 > 2 B 700 > 2 C 300 > 2 D 1500 > > ID AMT_BAYAR DATE_BYR > ------------ --- ------------ --- --------- > 2 1000 01-JAN-08 > 2 100 02-JAN-08 > 2 300 03-JAN-08 > 2 200 04-JAN-08 > 2 300 05-JAN-08 > 2 500 06-JAN-08 > 2 800 07-JAN-08 > 2 200 08-JAN-08 > 2 100 09-JAN-08 > > insert calc mjd : > ID DATE_BYR C AMT > 2 01-JAN-08 A 1000 > 2 02-JAN-08 B 100 > 2 03-JAN-08 B 300 > 2 04-JAN-08 B 200 > 2 05-JAN-08 B 100 > 2 05-JAN-08 C 200 > 2 06-JAN-08 C 100 > 2 06-JAN-08 D 400 > 2 07-JAN-08 D 800 > 2 08-JAN-08 D 200 > 2 09-JAN-08 D 100 > > so, dr cover table, dihabiskan dulu dari cover yg plng atas,kebawah per id (dihabiskan dulu amount cover A, lalu B,C & D) order by rownum > gmn yach scrptnya?? > > tx > rudi kristanto <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED]> create table cover ( > 2 id integer, > 3 cover varchar2(1), > 4 premium number(5) > 5 ); > > Table created. > > [EMAIL PROTECTED]> create table bayar ( > 2 id integer, > 3 amt_bayar number(5), > 4 date_byr date > 5 ); > > Table created. > > [EMAIL PROTECTED]> create table calc ( > 2 id integer, > 3 date_byr date, > 4 cover varchar2(1), > 5 amt number(5) > 6 ); > > Table created. > > [EMAIL PROTECTED]> insert into cover values (1,'A',1000); > > 1 row created. > > [EMAIL PROTECTED]> insert into cover values (1,'B',500); > > 1 row created. > > [EMAIL PROTECTED]> insert into cover values (2,'A',1000); > > 1 row created. > > [EMAIL PROTECTED]> insert into cover values (2,'B',700); > > 1 row created. > > [EMAIL PROTECTED]> insert into cover values (2,'C',300); > > 1 row created. > > [EMAIL PROTECTED]> insert into cover values (2,'D',1500); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (1,1200,sysdate-10); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (1,300,sysdate-9); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,1000,sysdate-10); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,100,sysdate-9); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,300,sysdate-8); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,200,sysdate-7); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,300,sysdate-6); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,500,sysdate-5); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,800,sysdate-4); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,200,sysdate-3); > > 1 row created. > > [EMAIL PROTECTED]> insert into bayar values > (2,100,sysdate-2); > > 1 row created. > > [EMAIL PROTECTED]> commit; > > Commit complete. > > [EMAIL PROTECTED]> select * from cover order by 1,2; > > ID C PREMIUM > --------------- - --------------- > 1 A 1000 > 1 B 500 > 2 A 1000 > 2 B 700 > 2 C 300 > 2 D 1500 > > 6 rows selected. > > [EMAIL PROTECTED]> select * from bayar order by 1,3; > > ID AMT_BAYAR DATE_BYR > --------------- --------------- --------- > 1 1200 01-JAN-08 > 1 300 02-JAN-08 > 2 1000 01-JAN-08 > 2 100 02-JAN-08 > 2 300 03-JAN-08 > 2 200 04-JAN-08 > 2 300 05-JAN-08 > 2 500 06-JAN-08 > 2 800 07-JAN-08 > 2 200 08-JAN-08 > 2 100 09-JAN-08 > > 11 rows selected. > > [EMAIL PROTECTED]> select * from calc; > > no rows selected > > [EMAIL PROTECTED]> edit hitung.sql > > [EMAIL PROTECTED]> @hitung > Enter value for id: 1 > old 29: > where id = &id > new 29: > where id = 1 > > 3 rows created. > > [EMAIL PROTECTED]> @hitung > Enter value for id: 2 > old 29: > where id = &id > new 29: > where id = 2 > > 11 rows created. > > [EMAIL PROTECTED]> l > 1 insert into calc > 2 select e.* from > 3 ( > 4 select id, date_byr, cover, > 5 hasil-nvl(lag(hasil) over (partition by > cover order by rn),0) amt > 6 from > 7 ( > 8 select rn, id, date_byr, cover, > 9 case when amt_cum >= prem_cum then > premium > 10 else > decode(sign(premium-(prem_cum-amt_cum)),-1,0,premium-(prem_cum- amt_cum)) > 11 end hasil > 12 from > 13 ( > 14 select b.*, > 15 sum(amt_real) over (order by rn) > amt_cum, > 16 sum(premium) over (partition by gid > order by rn) prem_cum > 17 from > 18 ( > 19 select rownum rn, a.* > 20 from > 21 ( > 22 select b.gid, b.id, b.amt_bayar, > b.date_byr, a.cover, a.premium, > 23 case when b.gid = lag(b.gid) > over (order by b.gid) > 24 then 0 > 25 else amt_bayar > 26 end amt_real > 27 from cover a, > 28 (select i.*, rownum gid from > (select * from bayar > 29 > where id = &id > 30 > order by date_byr, amt_bayar desc) i) b > 31 where a.id = b.id > 32 order by b.gid, a.premium desc > 33 ) a > 34 ) b > 35 ) c > 36 ) d > 37 ) e > 38 where e.amt > 0 > 39* order by date_byr, cover > [EMAIL PROTECTED]> select * from calc where id = 1; > > ID DATE_BYR C AMT > --------------- --------- - --------------- > 1 01-JAN-08 A 1000 > 1 01-JAN-08 B 200 > 1 02-JAN-08 B 300 > > 3 rows selected. > > [EMAIL PROTECTED]> select * from calc where id = 2; > > ID DATE_BYR C AMT > --------------- --------- - --------------- > 2 01-JAN-08 D 1000 > 2 02-JAN-08 D 100 > 2 03-JAN-08 D 300 > 2 04-JAN-08 A 100 > 2 04-JAN-08 D 100 > 2 05-JAN-08 A 300 > 2 06-JAN-08 A 500 > 2 07-JAN-08 A 100 > 2 07-JAN-08 B 700 > 2 08-JAN-08 C 200 > 2 09-JAN-08 C 100 > > 11 rows selected. > > [EMAIL PROTECTED]> spool off; > > --- risti <[EMAIL PROTECTED]> wrote: > > > Temans, sy ada problem utk memecah data insert ke > > suatu table, > > sample casenya spt ini : > > > > cover table > > id Cover Premium > > 1 A 1000 > > 1 B 500 > > > > Bayar table > > ID Amt_bayar date_byr > > 1 1200 01-Jan-07 > > 1 300 02-Jan-07 > > > > insert ke calc table jadi spt ini : > > Cover amt date_byr > > A 1000 01-Jan-07 > > B 200 01-Jan-07 > > B 300 02-Jan-07 > > > > gimana ya ngakalinnya?? jadi akan cek berapa > > amount covernya per cover, dan amount di table bayar > > akan dipecah2 sesuai maksimal dari amount di table > > bayar... > > > > thanks ya sblmnya > > > > > > --------------------------------- > > Never miss a thing. Make Yahoo your homepage. > > > > [Non-text portions of this message have been > > removed] > > > > > > Send instant messages to your online friends http://uk.messenger.yahoo.com > > > > > > --------------------------------- > Looking for last minute shopping deals? Find them fast with Yahoo! Search. > > [Non-text portions of this message have been removed] >
