suppose I have a oracle query
SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = ?
AND DESIGNATION = ?
AND DISTRICT = ?
AND CIRCLE = ?
And quite possible any 1 or 2 or 3 of the parameter (?) can be empty or null.
so what should I do so that the empty parameters are totally "ignore" in the where clause and only search for the non-empty parameter in the table.
How can I achieve this
Please help.. The query must be compatible oracle 10g. Thanks
You can rewrite query like:
select *
from EMPLOYEE
where (DEPARTMENT = p1 or p1 is null)
and (DESIGNATION = p2 or p2 is null)
and (DISTRICT = p3 or p3 is null)
and (CIRCLE = p4 or p4 is null)
or:
select *
from EMPLOYEE
where DEPARTMENT = nvl(p1, department)
and DESIGNATION = nvl(p2, designation)
and DISTRICT = nvl(p3, district)
and CIRCLE = nvl(p4, circle)
As @mathguy mentioned in comments second version will not show null values. Please use first version.
nvl version.NVL will be your friend here.
This function takes two input parameters and returns either the first one, or the second one if the first one is NULL.
This would work :
SELECT *
FROM EMPLOYEE
WHERE DEPARTMENT = NVL(yourParam1,DEPARTMENT)
AND DESIGNATION = NVL(yourParam2,DESIGNATION )
AND DISTRICT = NVL(yourParam3,DISTRICT )
AND CIRCLE = NVL(yourParam4,CIRCLE )
LNNVL - descript how lnnvl evaluates values.
!= - it is correct. I've not do the mistake here.
Why lnnvl better from column_name = nvl(param,column_name) .
if column has null value and param has null value.
null = null => false this row will be exluded from result.
select * from table
where lnnvl(column_name1 != ?)
and lnnvl(column_name2 != ?)
.
.
.
Using COALESCE:
SELECT *
FROM EMPLOYEE
WHERE COALESCE(DEPARTMENT,0) = COALESCE(:yourParam1,DEPARTMENT,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam2,DESIGNATION,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam3,DISTRICT,0)
AND COALESCE(DESIGNATION,0) = COALESCE(:yourParam4,CIRCLE,0)
The answer using Coalesce is genius. BUT, the zeroes need single quotes around them. Especially in this example were the parameters are likely to not be numbers, it will throw off the SQL because the actual field is a string but the 0 without quotes is a number. I applied this to my code and it worked like a charm. Recommendation:
SELECT *
FROM EMPLOYEE
WHERE COALESCE(DEPARTMENT,'0') = COALESCE(:yourParam1,DEPARTMENT,'0')
AND COALESCE(DESIGNATION,'0') = COALESCE(:yourParam2,DESIGNATION,'0')
AND COALESCE(DISTRICT,'0') = COALESCE(:yourParam3,DISTRICT,'0')
AND COALESCE(CIRCLE,'0') = COALESCE(:yourParam4,CIRCLE,'0')
Thank you so much, Mohammad Osman Gani Faisal!!!
Using decode:
SELECT *
FROM EMPLOYEE
WHERE decode(p_DEPARTMENT, NULL, 1, DEPARTMENT, 1, 0) = 1
AND decode(p_DESIGNATION, NULL, 1, DESIGNATION, 1, 0) = 1
AND decode(p_DISTRICT, NULL, 1, DISTRICT, 1, 0) = 1
AND decode(p_CIRCLE, NULL, 1, CIRCLE, 1, 0) = 1
where (c1 = :param or :param is null) and (c2 = ....ANDuseORso that either of the conditions are matched.