How to Return Multiple Values from CASE clause in Where Condition

Question

I have a codition in which i need to use multiple parameters in where clause using Conditional operator. I have written the following query and in this condition i can't use in clasue. Kindly guide me how can i return multiple parameters from case clause.

select * from cardimport
where
STATUS = CASE
WHEN STATUS = ''
THEN 'F'
ELSE STATUS
END

When Status in null i want to return 'F' and 'V'. Right now its returning Only 'F'

EDITED

select *
from CARDIMPORT
where STATUS =   CASE
WHEN $P{status} = ''
THEN 'E'
ELSE $P{status}
END

When the STATUS is null, I want to show the records of from all status, that are 'E', 'I', 'A'

Answer 1

I think you don't need CASE, rather you need NVL and NVL2.

If I understand your requirement correctly, you need a particular row when the status value is NULL, else when not null then return all the rows.

For example, in the below example from EMP table. If the parameter value is NULL, I expect only that row where comm = 300 else when not null then return all rows.

NULL

SQL> VAR a NUMBER;
SQL> EXEC :a := NULL

PL/SQL procedure successfully completed.

SQL> SELECT empno, comm FROM emp WHERE NVL(comm, 9999) = NVL2(:a, NVL(comm, 9999), 300);

     EMPNO       COMM
---------- ----------
      7499        300

NOT NULL

SQL> EXEC :a :=1400

PL/SQL procedure successfully completed.

SQL> SELECT empno, comm FROM emp WHERE NVL(comm, 9999) = NVL2(:a, NVL(comm, 9999), 300);

     EMPNO       COMM
---------- ----------
      7369
      7499        300
      7521        500
      7566
      7654       1400
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

SQL>

The above uses the following logic:

• If, the value passed is NULL, then return row where COMM = 300.
• Else if, value passed is NOT NULL, then return all the rows.