Plsql conditional where clause
I would like to execute a conditional where
in a sql statement and use two different criteria for example. in pseudocode:
procedure(..., bool_value IN boolean default false) is
....
begin
select * from mytable mt
where
if bool_value = true then mt.criterion_1 = value
else
mt_.criterion_2 = value; -- if boolean value is set, use criterion_1 column, otherwise use criterion_2 column
end
Suppose maybe what is the best way to do this?
thank
source to share
Try the following:
bool_value_string varchar2(5)
bool_value_string = case when bool_value then 'true' else 'false' end;
select * from mytable mt
where
(bool_value_string = 'true' and mt.criterion_1 = value)
or
(bool_value_string = 'false' and mt.criterion_2 = value)
Basically, convert your when ... then an idiom to ... or one. Either the boolean field is not null and true, that is, the filter must be according to the first criterion, or this does not mean that the filter is the second.
source to share
Basically, your condition translates to:
if bool_value = true
then mt.criterion_1 = value
else if bool_value = false
then mt_.criterion_2 = value;
Since you cannot directly use boolean in select statements (see comments) use as below: (Change bool_value from boolean to varchar2 or number)
procedure(..., bool_value IN varchar2(10) default 'FALSE') is
....
begin
select * from mytable mt
where
(case
when (bool_value = 'TRUE' and mt.criterion_1 = value) then (1)
when (bool_value = 'FALSE' and mt_.criterion_2 = value) then (1)
(else 0)
end) = 1;
OR
select * from mytable mt
where
(bool_value = 'TRUE' and mt.criterion_1 = value)
or
(bool_value = 'FALSE' and mt.criterion_2 = value)
end
ORIGINAL ANSWER
You can also use case statement
in where
as shown below:
select * from mytable mt
where
(case
when (bool_value = true and mt.criterion_1 = value) then (1)
when (bool_value = false and mt_.criterion_2 = value) then (1)
(else 0)
end) = 1;
In oracle, you can also use below Query.
select * from mytable mt
where
(bool_value = true and mt.criterion_1 = value)
or
(bool_value = false and mt.criterion_2 = value)
Note. Since the default is a condition bool_value = false
, there is is null
no need to check.
source to share