Requirement : Consider Emplyoee table emp which has Emplyee number and Deparment number columns.
Table : emp
empno deptno
---------------------
1 10
2 20
3 20
4 10
5 30
Based on some input parameter var that isnot part of any table we wish to select employees of specific Departments.
If value of var = 1 then
select dept 10 employees
empno 1 and 4
If value of var = 2 then
select dept 20 and 30 employees.
empno 2, 3 and 5
var variable has no significance with respect to Employee data
Solution :
[sourcecode language="sql"]
SELECT empno,deptno
FROM emp
WHERE deptno IN (DECODE(:var,1,10),decode(:var,2,20),decode(:var,2,30))
[/sourcecode]
when var value is 1 then
both second and third decode returns null, and evaluates to false for all rows, and first decode returns 10. The where clause translates into something like
where deptno = 10 or deptno = null or deptno = null
So, it returns deptno 10 employees only.
Similarly, for var value of 2 the where clause translates into
where deptno = null or deptno = 20 or deptno = 30
and returns deptno 20 and 30 employees.
No comments:
Post a Comment