Checking the Oracle SQL DECODE function result against 0

November 18, 2021

I’m looking at some old PL/SQL code and I have dozen of DECODE functions written like this:

DECODE(value_1, value_2, 1, 0) = 0

Now, I know these DECODEs make comparison between value_1 and value_2 and they return true or false based on the outcome of comparison. But, for the love of coding, could someone please tell me what’s the logic behind = 0 part? Why is it necessary to check the return value against 0?

>Solution :

If value_1 = value_2, then decode returns 1
otherwise, it returns 0

Therefore, the whole expression checks whether value_1 is different from value_2 and then – I presume – does something.

If it is part of PL/SQL, you could rewrite it to

if value_1 <> value_2 then...

Some examples:

SQL> declare
  2    value_1 number := 5;
  3    value_2 number := 1;
  4  begin
  5    decode(value_1, value_2, 1, 0) = 0
  6  end;
  7  /
  decode(value_1, value_2, 1, 0) = 0
                                 *
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( % ;


SQL> declare
  2    value_1 number := 5;
  3    value_2 number := 1;
  4  begin
  5    decode(value_1, value_2, 1, 0) = 0;
  6  end;
  7  /
  decode(value_1, value_2, 1, 0) = 0;
                                 *
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( % ;
The symbol ":= was inserted before "=" to continue.


SQL> declare
  2    value_1 number := 5;
  3    value_2 number := 1;
  4  begin
  5    if decode(value_1, value_2, 1, 0) = 0 then
  6       null;
  7    end if;
  8  end;
  9  /
  if decode(value_1, value_2, 1, 0) = 0 then
     *
ERROR at line 5:
ORA-06550: line 5, column 6:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


SQL> declare
  2    value_1 number := 5;
  3    value_2 number := 1;
  4  begin
  5    case when decode(value_1, value_2, 1, 0) = 0 then
  6         null;
  7    end case;
  8  end;
  9  /
  case when decode(value_1, value_2, 1, 0) = 0 then
            *
ERROR at line 5:
ORA-06550: line 5, column 13:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored


SQL> declare
  2    value_1 number := 5;
  3    value_2 number := 1;
  4    l_decode_result number;
  5  begin
  6    select decode(value_1, value_2, 1, 0)
  7      into l_decode_result
  8      from dual;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>