Waras Mad. ko see yoo
________________________________ Dari: Hermawan Dephe <[email protected]> Kepada: [email protected] Terkirim: Sel, 3 Agustus, 2010 14:13:01 Judul: [indopb] Re: LOOPING dalam SQL Eh iya.. hasil syntax tadi intinya saya cuma ingin menampilkan list tgl tgl di bulan ini (misal bulan ini = 201009) maka tgl2nya adalah : WK_DAY 1 20100901 2 20100902 3 20100903 4 20100904 5 20100905 .....dst 29 20100929 30 20100930 Hermawan Dephe wrote: Dear PBers >Mau nanya nih.. > >Saya punya syntax SQL pake oracle kayak gini nih.. >------------ --------- --------- --------- --------- --------- --------- >--------- --------- --------- ---- >select distinct obu,dept, b.wk_day, >0, 0, 0, 0, to_char(sysdate, 'yyyymmdd' ), to_char(sysdate, >'yyyymmdd' >), >0, 0, 0, 0 >from tkupon a, ( > select * from ( > select to_char(to_date( '201009'| |'01','yyyymmdd' )+0,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+1,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+2,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+3,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+4,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+5,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+6,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+7,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+8,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+9,'yyyymmdd' ) > wk_day >from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+10,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+11,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+12,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+13,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+14,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+15,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+16,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+17,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+18,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+19,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+20,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+21,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+22,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+23,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+24,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+25,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+26,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+27,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+28,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+29,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+30,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+31,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+32,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+33,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+34,'yyyymmdd' ) >wk_day from dual > union > select to_char(to_date( '201009'| |'01','yyyymmdd' )+35,'yyyymmdd' ) >wk_day from dual > ) > where wk_day like '201009'||'% ') b >where a.yyyymmdd=' 20100101' >order by obu,dept, wk_day >------------ --------- --------- --------- --------- --------- --------- >--------- --------- --------- ---- > > >Syntax SQL tersebut saya kira terlalu panjang dan hanya mengulang ulang >berkali >kali >" select to_char(to_date( '201009'| |'01','yyyymmdd' )+ :X ,'yyyymmdd') wk_day >from dual " > >Kalo diringkas pake LOOPING gitu ada gak sih di SQL? > >nyoba gak jadi2... :( > >Tengkyu sebelum dan sesudahnya.. . > > > >Regards, > >Hermawan Dony Prasetyo >IT Department >PT. LP Displays Indonesia >Phone :(62-21) 8989-511 >Fax :(62-21) 8980-282 >email : hermawan.dp@ lpdisplays. com > > >
