0

I need to write a query where I should Ignore any filter on that column if the respective parameter is Null, but should filter with IN clause if the said parameter is not null. I am trying to use the below query but I am not able to make it work. It is the HR DB and Employees table in Oracle 11 XE and I am trying to pass Job ID as a param and this param could be null or it could contain multiple values.

What I have done so far -

SELECT * FROM HR.EMPLOYEES
WHERE
CASE WHEN NVL(:PARAM_JOB_ID,'NONE')= 'NONE' THEN 'NONE' ELSE JOB_ID END IN NVL(:PARAM_JOB_ID,'NONE');

Please guide.

Ezio
  • 376
  • 5
  • 21
  • Similar questions with some additional discussion of the drawbacks of `OR` and `is null` approach [here](https://stackoverflow.com/a/57895363/4808122) and [here](https://stackoverflow.com/a/56778621/4808122) – Marmite Bomber Jul 23 '21 at 07:12
  • What application is passing/receiving these parameters? Oracle Reports, Apex etc? A simple text parameter like `:param_job_id` can't be used for an `in` list. For that you would need either an array or XML or JSON or some extra code to split it into its components. – William Robertson Jul 23 '21 at 15:31

3 Answers3

0

I would use IS NULL logic here:

SELECT *
FROM HR.EMPLOYEES
WHERE JOB_ID IN (:PARAM_JOB_ID) OR :PARAM_JOB_ID IS NULL;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • @timbiegleisen I am getting SQL Error [920] [42000]: ORA-00920: invalid relational operator. I passed PU_CLERK and ST_MAN as parameters and the final query looked like this - SELECT * FROM HR.EMPLOYEES WHERE JOB_ID IN ('PU_CLERK','ST_MAN') OR 'PU_CLERK','ST_MAN' IS NULL – Ezio Jul 23 '21 at 04:32
  • even if i put braces in the later part of :param_job_id it is still throwing an error - SELECT * FROM HR.EMPLOYEES WHERE JOB_ID IN ('PU_CLERK','ST_MAN') OR ('PU_CLERK','ST_MAN') IS NULL .....Invalid relational operator error – Ezio Jul 23 '21 at 04:35
  • 1
    @Ezio - You can't pass multiple choices as a single parameter to an `in` condition like that. That has nothing to do with the question you asked, which was about the `null` case. The passing of multiple choices the way you are trying is a separate and unrelated problem and you would run into it even if you didn't care about the `null` case. –  Jul 23 '21 at 06:11
  • @mathguy I suggest posting that as an answer. I answered more from an application prepared statement point of view, which seems to not be the case here. – Tim Biegeleisen Jul 23 '21 at 06:13
  • @mathguy I mentioned in my question that I want to pass multiple values in my parameter, I tried the below query and it worked - SELECT * FROM HR.EMPLOYEES WHERE JOB_ID IN (:PARAM_JOB_ID); The question still remains how can I switch between 2 if the param is null or not.. – Ezio Jul 23 '21 at 06:15
  • 1
    @Ezio - How did the query "work"? What, exactly, did you pass in as the bind variable? Two distinct strings (each enclosed in single-quotes), separated by comma? If you were able to do that, it would be very interesting to understand how that was possible. I have never seen that. Or are you, in fact, using a substitution variable (beginning with an ampersand, not a colon)? –  Jul 23 '21 at 06:21
  • 1
    @TimBiegeleisen - if, as it turns out, the OP is trying to use multiple values in the `in` condition, that is a very frequently asked question; I wouldn't offer an answer, but instead close the question as a duplicate. But the OP is reporting some miraculous behavior, where he is able to pass a list of individual values as a bind variable; waiting to understand what he means. –  Jul 23 '21 at 06:23
  • @mathguy thanks for waiting and not closing it prematurely, my question has 2 parts - a) if i am passing null in my parameter then the condition should not evaluate and I should get all records(assuming this is the only condition in my where clause). b) I need to pass multiple values in my parameter, I am not very versed with Oracle and I want the IN clause to work as my parameter could have comma separated multiple values. I am feeling a bit lost here. – Ezio Jul 23 '21 at 07:10
0

You'll have to split values in :PARAM_JOB_ID into rows. Something like this (Scott's sample schema and its EMP table):

select job, ename
from emp
where (   job in (select trim(regexp_substr(:param_job_id, '[^,]+', 1, level))
                  from dual
                  connect by level <= regexp_count(:param_job_id, ',') + 1
                 )
       or :param_job_id is null
      )
order by job, ename;

Demonstration in SQL*Plus:

SQL> select job, ename
  2  from emp
  3  where (   job in (select trim(regexp_substr('&&param_job_id', '[^,]+', 1, level))
  4                    from dual
  5                    connect by level <= regexp_count('&&param_job_id', ',') + 1
  6                   )
  7         or '&&param_job_id' is null
  8        )
  9  order by job, ename;
Enter value for param_job_id:                 --> empty parameter returns all rows

JOB       ENAME
--------- ----------
ANALYST   FORD
ANALYST   SCOTT
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER
CLERK     SMITH
MANAGER   BLAKE
MANAGER   CLARK
MANAGER   JONES
PRESIDENT KING
SALESMAN  ALLEN
SALESMAN  MARTIN
SALESMAN  TURNER
SALESMAN  WARD

14 rows selected.

SQL> undefine param_job_id
SQL> /
Enter value for param_job_id: CLERK, PRESIDENT

JOB       ENAME
--------- ----------
CLERK     ADAMS
CLERK     JAMES
CLERK     MILLER
CLERK     SMITH
PRESIDENT KING

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

Following works for me -

WHERE
(column IN COALESCE(:list_var, column) OR column IN (:list_var))

If the value is null, it will not cross the or condition, and will match all the rows effectively skipping this clause. If value exist, with the first part of condition it will match one of the result (basically whatever value COALESCE is able to find first, and later part of the clause will take care of the rest.

Hope this helps someone who is stuck with a similar problem.

Ashwani Agarwal
  • 1,279
  • 9
  • 30