Thursday, January 11, 2018

Oracle Case statements when testing for NULL

Just a little tidbit when working with ORACLE and case statements.

The first method, where the given column name is passed to successive WHEN statements, WHEN must have a value to work with, so you have to convert the null value into something and test for it.

Method 1: Covert the incoming value first

CASE nvl(some_column,'NULL')
         WHEN 'NULL' THEN some_other_column
         WHEN 'ABC' THEN another_column
         ELSE
          some_column
         END some_column

Method 2: do the condition checks in the WHEN
CASE 
    WHEN nval(some_column,'NULL') = 'NULL' THEN some_other_column
     WHEN some_column = 'ABC' THEN another_column
     ELSE some_column
     END  some_column