This content has been marked as final. Show 7 replies
Coalesce can take multiple arguments. NVL only two arguments
Thanks a lot
In addition take a look at
for NVL2 function aslo .
Coalesce is more strict with datatypes:
SQL> select nvl('2',1) from dual / N - 2 1 row selected. SQL> select coalesce('2',1) from dual * Error at line 1 ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
NVL is strict as far datatype is concerned .
Only thing is that NVL does the automatic datatype conversion .
SQL> select NVL(1,'9') from dual;
SQL> select NVL(1,'A') from dual;
select NVL(1,'A') from dual
ERROR at line 1:
ORA-01722: invalid number
No difference to coalesce there. Also note that you got an other ORA error as the one michaels showed..
"ORA-01722: invalid number " <> "ORA-00932: inconsistent datatypes: expected CHAR got NUMBER"
I think you missed my point .
What I wanted to explain
Both COALESCE and NVL need the all parameter are of same type.
Other wise will give Errors ( error type might be different )
And NVL is more flexible than COALESCE as it will try to auto convert the datatype , whiel Coalesce doesn't do that .
By stating that, I was agreeing with Michaels . :)