Catering for NULLS in comparitive statements
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Catering for NULLS in comparitive statements

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Catering for NULLS in comparitive statements

    Amazing how your SQL gets rusty, but here goes.

    Have a test that evaluates whether a value has changed in a trigger. Basically, if it has changed I do something. It can be NULL so I have to test if its been changed from NULL to a value and vice versa, as well as checking if values existed before and after the change, but are different.

    The old and new referred to below are the before and after values of the same column in a DB trigger.


    I have two methods:

    (1) IF
    NVL(old,'XXXX') <> NVL(new,'XXXX') THEN ...
    or

    (2) IF
    old <> new OR
    (old IS NULL AND new IS NOT NULL) OR
    (old IS NOT NULL AND new IS NULL) THEN ...

    (1) would work but I don't like coding the NVL alternative. I mean what if the value really was 'XXXX'! (Highly unlikely)

    (2) works but seems cumbersome.

    Is there a better, recommended method?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    How about creating a function (preferably three overloaded packaged functions for the three main datatypes - number, date, string) and implement whichever method you prefere in it. Then simply call that function with new and old values wherever you want to check if column vas changed.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2004
    Posts
    162
    I like the function call suggestion on the basis of readability etc. but the simple IS NULL logic is far more efficient. The most succinct expression of this logic i have seen is probably...

    IF :OLD.column_name != :NEW.column_name OR (:OLD.column_name IS NULL) != (:NEW.column_name IS NULL) THEN...

    Interestingly there is an undocumented function called sys_op_map_nonnull which is suitably overloaded and used (see also sys_op_descend, sys_op_countchg etc.) by DBMS_STATS for handling this comparison. Standard undocumented caveats apply.

    IF sys_op_map_nonnull (:OLD.column_name) != sys_op_map_nonnull (:NEW.column_name) THEN ...

    Padders

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Excellent suggestions chaps.

    And what do you think of using " <> " versus " != " ...?

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    I see '!=' used most commonly in PL/SQL hence I suspect that would be most readable in the sense that it is recognizable. Not that there is anything wrong with with '<>' and performance wise I would hope there is no difference.

    Of course if you want to be different why not try '^=' or '~='?

    Padders
    Last edited by padders; 02-25-2004 at 06:39 AM.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    '<>' is the only version given by my ANSI SQL 92 quick reference. I find it esthetically pleasing too.

    '!=' is used in other langauges.

    They behave identically in Oracle, so you pays your money and takes your choice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width