Funny comparisions :)

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;

(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;

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!

Regards,
  Michael. Received on Wed Dec 29 1999 - 08:40:42 CST