Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2010
    Posts
    4

    Question Unanswered: NULL value check in DB2

    Hi,

    I have a requirement to check whether IS [NOT] NULL function like anything in DB2, to check whether it is NULL

    in oracle we can use
    SELECT a FROM abc
    WHERE IS NOT NULL

    is there any thing similar in DB2 which can be used in a WHERE clause
    please help

    Saiju

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ssaiju View Post
    is there any thing similar in DB2 which can be used in a WHERE clause
    yes, you can do the same in DB2

    however, your oracle example is not quite right --
    SELECT a FROM abc
    WHERE IS NOT NULL
    this is incorrect in any database system

    it should be like this --
    Code:
    SELECT a FROM abc 
    WHERE something IS NOT NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    My tutor once said: "think of that little river in austria: the INN" when I coded someting like "not is null" which was rejected as valid SQL

  4. #4
    Join Date
    Dec 2010
    Posts
    4

    Question

    thanks for the reply & correction

    SELECT a FROM abc
    WHERE smething IS NOT NULL


    is fetching NULL values as well in 9.7.

    Is there any specific setting needs to be done ..?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the exact query you ran, please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2010
    Posts
    4

    Question

    The below query returns null values in DB2 9.7

    select NEG_INSTR_SER_NO from journl_entry_item where NEG_INSTR_SER_NO is not null

    Any other alternative to check null values ..?

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Are you confusing empty strings with nulls, may be?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by n_i View Post
    Are you confusing empty strings with nulls, may be?
    not his fault, really, coming from oracle which doesn't know the difference
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    4

    Question

    the same dataset behaves differently in oracle and DB2 for the same query ..
    i am just trying to find the difference in implementation

    How the DB2 differentiate an empty string compared to oracle ..?

  10. #10
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    an empty string (null string/string with length zero) is = '' (or = "")

    null is a column attribute. a column may contain a
    'null string/empty string/varchar length 0/fixed length all spaces'
    but if a column is null, then there is no value/data to check, you check the attribute,
    which in db2 is IS COLUMN NULL.
    Dick Brenholtz, Ami in Deutschland

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you mean you loaded data from a file into a DB2 table and you are now trying to find records that are not null and you are finding records that should be null? If that is the case, then you should look at your load control statement to see if the null indicator value was identified for the load process.
    Dave

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In Oracle, testing = '' (two single quotes) is the same as testing for null. This is not in accordance with ANSI SQL standards and is not supported by normal DB2 syntax, however, I am not sure about Oracle compatibility mode in DB2 9.7.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Tags for this Thread

Posting Permissions

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