6

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

2787184
  • 3,749
  • 10
  • 47
  • 81
anikesh sharma
  • 97
  • 1
  • 2
  • 8
  • you can build up a query template depending on the paramenter value you have – Alexey R. Aug 25 '17 at 12:44
  • 1
    Something like `where (c1 = :param or :param is null) and (c2 = ...`. – jarlh Aug 25 '17 at 12:45
  • Inplace of `AND` use `OR` so that either of the conditions are matched. – XING Aug 25 '17 at 12:45
  • 2
    @XING - Seriously? you think AND and OR can be used interchangeably? –  Aug 25 '17 at 12:49
  • Possible duplicate of [How to ignore null parameter in a Stored Procedure Oracle](https://stackoverflow.com/questions/42151820/how-to-ignore-null-parameter-in-a-stored-procedure-oracle) – Ponder Stibbons Aug 25 '17 at 12:50
  • @mathguy It depends what you need. – XING Aug 25 '17 at 12:50
  • Exactly. The OP needs AND and you suggest to use OR instead. Why? –  Aug 25 '17 at 12:52
  • @mathguy What OP needs, only OP can tell. I would say don;t be in a great hurry and decide yourself what OP needs. Question however is not pretty clear to me. If you feel you understood better you can propose your solution – XING Aug 25 '17 at 12:54
  • The OP already told us what he needs: he needs to handle NULL. I don't need to "propose my solution" since two perfectly fine solutions have already been proposed. What I am talking about here is your suggestion, which makes no sense. –  Aug 25 '17 at 12:55
  • @mathguy Then please upvote it and move on – XING Aug 25 '17 at 12:56

6 Answers6

11

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.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
  • Will the second query be able to use column indexes or would the use of a function cause full table scans? – MT0 Aug 25 '17 at 12:53
  • 2
    What will the second version do if CIRCLE (say) is NULL in the base table? Is that the desired behavior? The correct answer is the first version you proposed. –  Aug 25 '17 at 13:01
  • @MT0 - why only the second query? You may ask the same question about the first one too. The answer is the same. –  Aug 25 '17 at 13:04
  • I made short test and index was used in `nvl` version. – Ponder Stibbons Aug 25 '17 at 13:05
  • @mathguy Often when a function is used to filter the query this will prevent the use of an index (and require a function-based index) - however the query parser may handle `NVL` differently and rewrite the function to something like `((p4 IS NOT NULL AND CIRCLE = p4) OR (p4 IS NULL AND CIRCLE = CIRCLE))`. – MT0 Aug 25 '17 at 13:12
  • @mathguy - You are right. Second version removes this row from output. And this is not desired. – Ponder Stibbons Aug 25 '17 at 13:13
  • 1
    @MT0 - My point was that "x = :param or :param is null" **also** prevents the use of an index. The first version is better than the second, but "ability to use an index" is not among the reasons. –  Aug 25 '17 at 13:19
1

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 )
Dessma
  • 599
  • 3
  • 11
  • @UsagiMiyamoto - Why? –  Aug 25 '17 at 12:51
  • 1
    @mathguy NVL evaluates both side, coalesce will do until a value is found. Basically, over large datasets an NVL will be slower. -- Info [here](https://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce) – JohnHC Aug 25 '17 at 12:53
  • @JohnHC - That is relevant if the second term of NVL requires any kind of evaluation. In this context it doesn't - DESIGNATION is evaluated already. Note also that NVL has been along much longer than COALESCE, and in some cases it may actually be faster. –  Aug 25 '17 at 12:58
  • 1
    @Dessma - What will happen if CIRCLE (for example) is NULL in the base table? Is that the desired result? (Probably not!) –  Aug 25 '17 at 13:05
1

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 != ?)
  .
  .
  .
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17
0

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
I3rutt
  • 574
  • 4
  • 18
  • If `p_CIRCLE = NULL` won't this just exclude all the values for `CIRCLE` that are non-`NULL` rather than acting as an optional parameter and allowing any `CIRCLE` value? – MT0 Aug 25 '17 at 13:20
  • @MT0 add changes – I3rutt Aug 25 '17 at 13:39
0

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)
Mohammad Osman Gani Faisal
  • 45
  • 1
  • 6
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!!!

Choua Yang
  • 1