CODEWITHSUNDEEP
javasqloracle-database
anikesh sharma
anikesh sharma

Reputation: 107

Oracle : sql select query if condition parameter is null then ignore the parameter

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

Upvotes: 6

Views: 17428

Answers (6)

Choua Yang
Choua Yang

Reputation: 1

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!!!

Upvotes: 0

Mohammad Osman Gani Faisal
Mohammad Osman Gani Faisal

Reputation: 45

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)

Upvotes: 0

I3rutt
I3rutt

Reputation: 584

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

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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.

Upvotes: 11

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

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 != ?)
  .
  .
  .

Upvotes: 1

Dessma
Dessma

Reputation: 609

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 )

Upvotes: 1

Related Questions