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.
 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, ' ')
    • Oh, and it's not just Oracle (and PostgreSQL) that treats NULL's this way.
    • And if you want to be SQL-92 compliant, you can use COALESCE, which works the same as NVL (but sounds better, even though it takes longer to type).

    • 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 :-)