CODEWITHSUNDEEP
sqloracle-database
sim
sim

Reputation: 3602

Can I use NULL or NOT NULL in an Oracle SQL parameter?

Let's say I have two Oracle SQL queries that I'm using in various places in my code:

select *
from employees
where manager_id IS NULL
and etc etc etc...

and

select *
from employees
where manager_id IS NOT NULL
and etc etc etc...

The only difference between the two queries is that manager_id is NULL in the first one, and NOT NULL in the second one.

Is there any way to write this as one generic statement? Is it possible to pass in NULL or NOT NULL in a parameter? Something like this:

select *
from employees
where manager_id = ?
and etc etc etc...

I know the above example doesn't work (and I know why). My question is more along the lines of is there a more elegant solution than managing two separate SQL strings that are 99% similar?

Upvotes: 1

Views: 200

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You would need to use more complex logic. Perhaps:

where ( (? = 'not null' and manager_id is not null) or
        (? = 'null' and manager_id is null)
      )

Upvotes: 8

Related Questions