Hi,

I have a requirement to check whether ISNULL function like anything in DB2, to check whether it is NULL. Plz provide if so.

Like NVL( ) in Oracle.

If you simply want to check
for null values use a predicate.

ex:

Select a from b
where a is (not) null

Otherwise please explain in detail,
what the Oracle function does.

BOW

You can use COALESCE() function of DB2. It is similar to NVL() function of Oracle.

ex:

Suppose you have a table TEST with the following values:

db2 => select * from test

ID AMT
----------- -----------
1 25
2 26
3 27
4 -

where amt for id=4 is a NULL value.

Now say, you want to replace the NULL value with 99 for ID=4. You have to issue the following SQl statement.

db2 => select id, COALESCE(amt,99) as amt from test

ID amt
----------- -----------
1 25
2 26
3 27
4 99

I think that will help u...

Regards,
Gautam Paul

If you like to test, if something is null, please write IS NULL

But there are this function:
IFNULL
- to change a NULL to a more meaningfull data. Like this:

SELECT IFNULL(END_DATE, '9999-12-31')
FROM TABLE WHERE KEY = unique_key-value

COALESCE function will do the same, but IFNULL is slightly faster.


Cheers, Bill

Hi,

Hurmavi, are you sure IFNULL exist in DB2 database? I think it does't. According to the web page and the following pages the IFNULL function is Ingres function for COALESCE.

Hope this helps,
Grofaty

Yes!

Quite certain! In DB2 OS/390 version 7 there are COALESCE, IFNULL and NULLIF functions. Probably they are found also from Ingress and many more, because they are defined in SQL-99 standard.

Cheers, Bill