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 [email protected], 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 <teguh4mi...@...>
> To: [email protected]
> 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]
>
