Can NVL Function be Cascaded?

Question

can nvl() function be cascaded,...it was asked me in IBM interview .....and why????

Answer 1

Why not? For example:

select NVL( null, NVL( null, 1 )) from dual

It can be something like:

select NVL( delete_date, NVL( edit_date, create_date ))  AS last_change
from Table

May be they wanted you to say that it is deterministic function. So it is reentrant.

Answer 2

i tnk nvl(nvl(sal,'no sal'),'no sal') is wong and has no meaning, but nvl(to_char(managerid),'no manager') is possibe as any single row fun can be nested

Answer 3

Better yet, use COALESCE

Answer 4

My answer:

Yes, NVL can be cascaded. It's something I'd expect to see on code ported from Oracle versions up to and including 8i, because COALESCE wasn't supported until Oracle 9i.

COALESCE is an ANSI SQL 99 standard, and works like a CASE/SWITCH statement in how it evaluates each expression in order and does not continue evaluating expressions once it hits a non-null expression. This is called short-circuiting. Another benefit of using COALESCE is that the data types do not need to match, which is required when using NVL.

This:

SELECT COALESCE( 1.5 / NULL, 
                 SUM(NULL), 
                 TO_CHAR(SYSDATE, 'YYYY') ) abc
  FROM DUAL

...will return: 2009 (for the next ~32 days, anyways)

Answer 5

The most common reason that I have seen for cascaded NVL's is that databases change over time. The original design had a table that later was changed to have more columns in the table. Alter statements created the new columns as NULL allowed and this was taken advantage of with view so that the code on top did not need to change. The problem was that a single column, change_date, was replaced with multiple columns. Update_Date, Comment_Date, and Approval_date. Each of the 3 new columns is now combined to get a "change_date" in the view with

create or replace view OldTableNmae as
select other_columns
    , nvl ( update_date, nvl ( comment_date, approval_date ) ) change_date
    , more_columns
from  new_table
/