Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Some Days I Feel Like I Know Nothing About Oracle 3 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • If you know that the possibly null column can not be some value (say a single space) or you want to assume null is the same as some value, then you can use:

    where col1 != NVL(col2, ' ')

    • Is that right?

      I am thinking right now that the correct way to write this test would be WHERE NVL(col1 != col2, FALSE). So it would return the resulting boolean value from all comparisons of existent values, but for comparisons involving NULL it would default the resulting NULL to FALSE. Basically you’re saying “compare the columns for inequality, and if they can’t be compared, then treat them as unequal.”

      By doing it this way you avoid semi-predicate problems.

      • I see what you're saying...except that the NVL() should return TRUE if the '!=' result is null (at least that's what the OP seems to want). And I know he said that only one of the columns could be null, but I'm so used to potentially both columns being null, and wanting to treat that case as the columns being equal, that I'm used to wrapping the columns in NVL() individually...and not thinking about brilliant shortcuts :-)
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.