Oracle CASE with OR

Question

Is it possible to use the CASE statement in Oracle with OR as follows:

CASE WHEN A> 0 OR B> 0 THEN c = 1 END;

I know that we can use AND, but I get an error when using OR. Can you suggest something? Thanks.

Answer 1

Have you tried putting your OR operator in parens?

 CASE WHEN (A > 0 OR B >0) THEN c=1 END;

Answer 2

You sent a CASE expression, but called it a CASE statement. Probably due to this confusion. The CASE expression is valid:

SQL> declare
  2    bool boolean;
  3    a    int := 1;
  4    b    int := 0;
  5    c    int := 1;
  6  begin
  7    bool := CASE WHEN A > 0 OR B >0 THEN c=1 END;
  8    if bool is null
  9    then
 10      dbms_output.put_line('NULL');
 11    elsif bool
 12    then
 13      dbms_output.put_line('TRUE');
 14    else
 15      dbms_output.put_line('FALSE');
 16    end if;
 17  end;
 18  /
TRUE

PL/SQL procedure successfully completed.

But you probably wanted to use a CASE statement that ends with "END CASE" instead of "END". Example:

SQL> declare
  2    a    int := 1;
  3    b    int := 0;
  4    c    int;
  5  begin
  6    case
  7    when a > 0 or b > 0 then
  8      c := 1;
  9    end case
 10    ;
 11    dbms_output.put_line(c);
 12  end;
 13  /
1

PL/SQL procedure successfully completed.

Regards, Rob.