What is NVL function?(m48)

Just wanted to know if nvl function can work with characters.....because i
tried to substitute null values with the word 'not available' but it didnot
work.it replaces the null value with '0' but doesnot insert characters.

Ans1:

Yes, the NVL does work with character types. It sounds like you are using a
number type in your NVL statement. This will not work b/c the value type you
return must be the same type as the field you are testing. In your case you
can not return a char type to a number type.


Ans2:

It will work with characters if the column in question is a
character column. If the column is numeric, then NVL must
return a numeric value. When you specify '0', Oracle does an
implicit conversion to make it a numeric zero.

If you need text, you might be able to do something like
this:

SQL> select nvl(to_char(test_num),'not available') from
test;

NVL(TO_CHAR(TEST_NUM),'NOTAVAILABLE')
----------------------------------------
0
not available

The above should work if you are using SQL*Plus, or some
similiar tool, to generate a report.

Jonathan
 
 

Hosted by www.Geocities.ws

1