Friday, May 25, 2012

DECODE inside IN clause Oracle SQL

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