CODEWITHSUNDEEP
sqloracle-database
Rahul
Rahul

Reputation: 2751

Oracle - Filter records based on input value

In below sql statement, value for the person_id is dynamically passed.

SELECT * FROM person WHERE person_id = '&PERSON_ID';

There is a requirement, when user enters % for person_id, all records should be returned. Simply it should execute following query:

SELECT * FROM person;

Can anyone know the trick?

Upvotes: 1

Views: 227

Answers (1)

Ruelos Joel
Ruelos Joel

Reputation: 2399

You can use Case statement in where clause to trick it.

If variable PERSON_ID is '%' then it will just equal to the same column, it's like 1 = 1 is true.

SELECT * FROM person WHERE person_id = (CASE WHEN '&PERSON_ID' = '%' THEN person_id ELSE '&PERSON_ID' END);

If variable PERSON_ID is empty not 'null', it will still display all result just like the below . 

SELECT * FROM person;

Upvotes: 1

Related Questions