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'
Best Answer
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 isNULL
, I expect only that row wherecomm
=300
else whennot null
then return all rows.NULL
NOT NULL
The above uses the following logic:
COMM = 300
.