Search code examples
sqloracle-databaseunixsun

'!=' operation when one of the field might be NULL, sql

I have a sql package here that is trigged when I insert or update one table. This is the critical part where (very simplified)

IF ( UPDATING ) THEN
        IF ( V_OLD_DATE  != V_NEW_DATE ) THEN
                  ---- do stuff

So, it works fine whenever the V_OLD_DATE is filled, however, V_OLD_DATE can be NULL and this is messing with the results of this trigger.

Is there any "cannonical" solution for this situation? The best I could do is a pre cheking of V_OLD_DATE

   IF ( UPDATING ) THEN
                IF ( (V_OLD_DATE is NULL and V_NEW_DATE is not NULL) OR 
                           V_OLD_DATE  != V_NEW_DATE ) THEN
                               ---- do stuff

Thank you

Solution

  • You can use NVL in Oracle. you can visit the link below for details:

    https://forums.oracle.com/thread/2186954