[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

oracle where clause

experts---
I have to code a where clause in oracle which has the following cases

1. If the input variable is null ignore this column in the where clause
2. if the input variable has value then include this column in the where clause.

ex: select a,b,c
      from table_a
where col_a = input1
and col_b = (if input2 has a value then do the and col_b = input2
else dont )

0
chickanna
Asked:
chickanna
2 Solutions
 
cyberkiwiCommented:
select a,b,c
from table_a
where col_a = input1
and (input2 is null OR col_b = input2)
0
 
johnsoneSenior Oracle DBACommented:

Alternately....
select a,b,c
  from table_a
 where col_a = input1
   and col_b = nvl(input2, col_b)

Open in new window

0
 
awking00Commented:
You can also use coalesce
select a,b,c
  from table_a
 where col_a = input1
   and col_b = coalesce(input2, col_b)
 
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now