Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: ISNULL function in DB2 - Urgent PLZ !

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    using a predicate

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Re: using a predicate

    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

  4. #4
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: ISNULL function in DB2 - Urgent PLZ !

    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

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 04-16-04 at 03:10.

  6. #6
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •