alhamdulilah dah bisa pak, mathur thank you banget atas jawabanya... --- Pada Rab, 24/6/09, Yulius Wibowo <[email protected]> menulis:
Dari: Yulius Wibowo <[email protected]> Topik: [indo-oracle] Re: OOT:Statment IF dalam filter query Kepada: [email protected] Tanggal: Rabu, 24 Juni, 2009, 11:22 PM Bisa digunakan : LIKE NVL('<parameter> ', '%') SQL> select * from emp where empno like NVL('&p_empno' , '%'); Enter value for p_empno: 7369 old 1: select * from emp where empno like NVL('&p_empno' , '%') new 1: select * from emp where empno like NVL('7369', '%') EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------ --- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 1 row selected. SQL> select * from emp where empno like NVL('&p_empno' , '%'); Enter value for p_empno: <--- BLANK = NULL old 1: select * from emp where empno like NVL('&p_empno' , '%') new 1: select * from emp where empno like NVL('', '%') EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------ --- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. bw --- In indo-oracle@ yahoogroups. com, Raja Krishna <raja_...@.. .> wrote: > > Dear Teguh, > > mgkn kita bisa menggunakan dengan cara CASE WHEN > contoh: > > > SELECT a.NIP,a.nama_ pegawai,a. alamat FROM tm_pegawai a > WHERE a.kd_bagian= (CASE WHEN <parameter>THEN< condition> END) > > mudah2an bisa membantu.. > thx > > > > > ____________ _________ _________ __ > From: Teguh Susanto <teguh4milis@ ...> > To: indo-oracle@ yahoogroups. com > Sent: Thursday, June 25, 2009 10:31:18 AM > Subject: [indo-oracle] OOT:Statment IF dalam filter query > > > > > > kepada Senior DBA dan milliser Oracle > mungkin kita sering menggunakan function DECODE,NVL,CASE dalama suatu query > terutama pada field2 yg akan ditampilkan > saya punya permasalahn tentang statment If,decode lain2nya didalam filter > suatu query > contoh > "SELECT a.NIP,a.nama_ pegawai,a. alamat FROM tm_pegawai a > WHERE a.kd_bagian= '<parameter> '",tapi kalau parameter tsb tidak ada > nilainya tentunya kosong, apakah ada semacam decode,statment IF atau cara yg > lain utntuk filter di query apabila parameter tsb tidak diisi jadi > seolah-olah " SELECT a.NIP,a.nama_ pegawai,a. alamat FROM tm_pegawai a" > sementara ini masih saya atasi disisi programingnya > atas perhatinnya sy ucapkan terimkasih > > teguh > > Nikmati chatting lebih sering di blog dan situs web. Gunakan Wizard Pembuat > Pingbox Online. http://id.messenger .yahoo.com/ pingbox/ > > [Non-text portions of this message have been removed] > > > > > > > > [Non-text portions of this message have been removed] > Terhubung langsung dengan banyak teman di blog dan situs pribadi Anda? Buat Pingbox terbaru Anda sekarang! http://id.messenger.yahoo.com/pingbox/ [Non-text portions of this message have been removed]
