psj01 psj01 - 1 year ago 82
SQL Question

sql query NVL string with apostrophes (')

So I have an sql query i am currently working on that is like this:

SELECT * from tableA where
( status = NVL('','OPEN') or status = NVL('','CLOSED') )
and delete_flag != 'Y'

The above query works fine and gives me the result I want.. but I was wondering if there is anyway I can combine the above status IN NVL line to one instead of using the or there.

for example, I want to be able to do:

SELECT * from tableA where
status IN NVL('','OPEN','CLOSED')
and delete_flag != 'Y'

But the apostrophes are not working with me here.. how can I work around it?

Answer Source

You are getting an input parameter from your application that can have the values "Open", "Closed" or null

You want to be able to select status values that equal this input paremeter if it is null or the value of the input if it isn't.

To have null for a filter default to all you use COALESCE and the column you are filtering on.

Like this

SELECT * from tableA
where COALESCE(parameter,status) = status
  and status in ('OPEN','CLOSED') -- see comments
  and delete_flag != 'Y'

In this case if parameter is OPEN you will get all OPEN items, if parameter is CLOSED you will get all closed items and if it is null you will get all items.

This is a very common pattern in web applications.

Single line version

SELECT * from tableA
where COALESCE(parameter,CASE WHEN status in ('OPEN','CLOSED') then status ELSE '' END) = status
  and delete_flag != 'Y'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download