Handling NULL values in GREATEST() function in Oracle Database

We can use GREATEST function to return the greatest value in a list of expressions. For example, below statement will return the value 3. ``` SELECT GREATEST(1, 2, 3) FROM DUAL; ``` However, if there is a NULL value in it, the return value will be NULL. As NULL is unknown, there is no way to do the comparison. If you call a SQL function with a null argument, then the SQL function automatically returns null. The workaround here is to use ``NVL`` to return an alternative value when an expression is NULL. Let's say we want to find out the max value between DATE 1 - 3. We can change the statement from ``` SELECT GREATEST( DATE1, DATE2, DATE3 ) FROM MY_TABLE; ``` to ``` SELECT GREATEST( NVL(DATE1, TO_DATE('1970-01-01', 'YYYY-MM-DD')), NVL(DATE2, TO_DATE('1970-01-01', 'YYYY-MM-DD')), NVL(DATE3, TO_DATE('1970-01-01', 'YYYY-MM-DD')) ) FROM MY_TABLE; ``` By adding ``NVL``, we can ensure that the expected return value is not NULL.