plsql conditional where clause

Question

I'd like to perform a conditional where in a sql statement, and use two different criteria, e.g. in pseudo code:

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 it's possible, What's the best way to do it?

Thanks

shree.pat18 and Nishanthi: thanks for your solutions, it works ! As pointed out, testing with Boolean won't work in Oracle, because SQL has no equivalent data type. See http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/datatypes.htm#LNPLS348 — Carmellose, Aug 07 '14 at 14:31

shree.pat18 · Accepted Answer · 2014-08-07T13:27:15.123

10

Try this:

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 idiom to an either...or one. Either the boolean field is non-null and true, meaning filter has to be by the first criterion, or it isn't, meaning filter by the second one.

edited Aug 07 '14 at 13:27

answered Aug 07 '14 at 11:59

shree.pat18

21,449
3
43
63

4

Unless something has changed very recently in Oracle, `bool_value = true` doesn't work; you can't use boolean variables in an SQL statement. – Erich Kitzmueller Aug 07 '14 at 12:22
1

@ammoQ This was meant to be pseudo-code, but you are right in pointing out the issue with booleans. I will update my answer to reflect that. – shree.pat18 Aug 07 '14 at 12:25
2

@ammoQ yes double thanks for pointing that out. Will stick as far clear of Oracle as possible now I know that. – simo.3792 Aug 07 '14 at 12:37
1

`bool_value is null` also doesn't work ` PLS-382: expression is of wrong type`. You have to keep your boolean variables totally out of the SQL statements. – Erich Kitzmueller Aug 07 '14 at 13:06
1

@ammoQ Damn forgot to remove that. Thanks again. – shree.pat18 Aug 07 '14 at 13:26

ngrashia · Answer 2 · 2014-08-07T12:32:24.200

Basically, your condition gets translated as:

 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 value in select statements (Refer comments), use as below: (Change bool_value from boolean to varchar2 or a 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 clause as 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 use below Query also.

  select * from mytable mt
  where 
  (bool_value = true and mt.criterion_1 = value)
   or
  (bool_value = false and mt.criterion_2 = value)

Note: Since default of bool_value = false, is null condition need not be checked.

You can't use boolean variables or literals `true`, `false` in an Oracle SQL statement. — Erich Kitzmueller, Aug 07 '14 at 12:24

score 1 · Answer 3 · answered Aug 07 '14 at 12:01

1

Simplest form is this:

WHERE (bool_value = TRUE AND mt.criterion_1 = value)
OR (bool_value = FALSE AND mt.criterion_2 = value)

answered Aug 07 '14 at 12:01

simo.3792

2,102
1
17
29

2

As already mentioned by ammoQ in response to shree.pat18's answer, this won't work, since Oracle doesn't support Booleans in SQL. – Frank Schmitt Aug 07 '14 at 12:38
1

@FrankSchmitt as already mentioned by shree.pat18, the answer was intended to be pseudo code as we did not know what data type the bool_value actually was. – simo.3792 Aug 07 '14 at 13:03

plsql conditional where clause

3 Answers3