Re: Funny comparisions :)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 29 Dec 1999 10:11:05 -0500
Message-ID: <vr8k6so86l64mesnubrrjqrv0l84k0os8f@4ax.com>

A copy of this was sent to "Michael Ju. Tokarev" <mjt_at_tls.msk.ru> (if that email address didn't require changing) On Wed, 29 Dec 1999 17:40:42 +0300, you wrote:

>Hi!
>
>Just want to notice -- accidentally I found very funny solution of comparision
>problem in sql and plsql. I think that this solution was already found by
>someone else, but I don't know them...
>
>The problem: if you need to compare (for equality) two values in sql or plsql,
>you need a bit complicated expression, that also can't fit inside one if/while
>statement. This is because value can be NULL, and comparision with NULL gives
>NULL result.
>
>For example, we have two plsql variables named a and b (type does not matter,
>it can be number, varchar or any other simple one). If you need to see it them
>are equal, you will wrote something like:
>
> if (a is null and b is not null) or (a is not null and b is null) then
> do_something_if_nonequal;
> elsif a is not null and b is not null then
> if a <> b then
> do_something_if_nonequal;
> else
> do_something_if_equal;
> end if
> else
> do_something_if_equal;
> enf if;
>

how about

  if ( a = b OR (a is null and b is null) )   then

• they are "equal" else
• they are not end if;

>(maybe this can be written bit simple, but anyway you can't have just one expression!)
>Another approach is to use function, e.g. compare, that return true/false, something like:
> if a is null then
> return b is null;
> elsif b is null then
> return false;
> else
> return a = b;
> end if;
>

and that can just be:

  return (a=b OR (a is null AND b is null));

>Here is another way (that funny one!):
>
> if decode(a, b, true, false) then
> do_something_if_equal;
> else
> do_something_if_nonequal;
> end if;
>
>Decode function, unlike all other operations, can manipulate "in correct way" on NULLs.
>This way is not very elegant (nor understandable), but it is much simple!
>

but that'll not work as decode is not callable from PL/SQL

SQL> declare

  2          a number;
  3          b number;
  4  begin
  5          if ( decode( a, b, true, false ) ) then
  6                  null;
  7          else
  8                  null;
  9          end if;

 10 end;
 11 /
declare
*
ERROR at line 1:
ORA-06550: line 5, column 7:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored

and even then TRUE and FALSE are plsql-isms, not understood in SQL (you would have to return 0/1 or Y/N or some type that SQL understands)

>Regards,
> Michael.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 29 1999 - 09:11:05 CST