[ Can NVL Function be Cascaded? ]
can nvl() function be cascaded,...it was asked me in IBM interview .....and why????
Answer 1
Better yet, use COALESCE
Answer 2
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 3
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 4
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
/
Answer 5
As others said, NVL can be cascaded, but the preferred solution would be to use COALESCE instead. However, the two functions are not entirely interchangeable:
1) as mentioned elsewhere, COALESCE only evaluates the arguments from the first until it meets one that does not evaluate to null
2) however, COALESCE requires all arguments to be of the same data type, thus being STRICTER than NVL, which will first attempt an implicit conversion.
E.g.
SELECT COALESCE( 1.5 / NULL,
SUM(NULL),
TO_CHAR(SYSDATE, 'YYYY') ) abc
FROM DUAL
throws in fact the error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
NVL would instead return the current year
SELECT NVL( 1.5 / NULL,
NVL( SUM(NULL),
TO_CHAR(SYSDATE, 'YYYY') ) ) abc
FROM DUAL
Other examples:
select coalesce('some text',sysdate) from dual;
throws ORA-00932: inconsistent datatypes: expected CHAR got DATE, while
select nvl('some text',sysdate) from dual;
returns some text, but
select nvl(sysdate,'some text') from dual;
throws ORA-01841: (full) year must be between -4713 and +9999, and not be 0 (because the implicit conversion attempt of 'some text' to a date has failed)
