0

I want to do a Query like this:

v_name VARCHAR2(60); SELECT * FROM book WHERE name = v_name

But if "v_name" is NULL, the program must be:

SELECT * FROM book

I don't wan't to set the filter in the WHERE condition. If v_name is NULL i dont want to get all the recodrs with name=NULL, but ALL the records.

I have tried this:

SELECT * FROM book WHERE name (CASE WHEN v_name IS NULL THEN name ELSE v_name END);

But it doesn't work.

Thanks.

Samuel
  • 11
  • 1

3 Answers3

2

One way to match null with all books when v_name is null or match only a book (assuming NAME is unique) when v_name is not null is to do it by typing fewer characters:

select *
  from BOOK
 where v_name is null
    or NAME = v_name

But for commercial apps, I think doing things like this is unwise. It's reasonable to force the user to think.

Jeff Holt
  • 2,940
  • 3
  • 22
  • 29
0

Here is your needed query structure;

SELECT
    *
FROM
    book 
WHERE (v_name is null) or 
      (v_name is not null and name = v_name);
Hamit Ünügür
  • 21
  • 3
0

I think NVL function can help you here -

v_name VARCHAR2(60); 
SELECT * 
  FROM book 
 WHERE name = NVL(v_name, name);

So, If v_name would be NULL, column would compare with itself and fulfills your purpose.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40