Oracle CASE with OR

Question

Hi All, Can we use CASE statement in Oracle with OR like this:

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

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

Answer 1

have you tried putting your OR statement in parens?

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

Answer 2

You posted a CASE expression, but named it a CASE statement. That's probably where the confusion comes from. 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 meant to use the CASE-statement, which ends with "END CASE" instead of "END". An 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.