Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    28

    Unanswered: Effects of NULL values

    OS390, DB2V7

    Just wanted to ask you guys regarding the effects of null values in queries...
    SQL below...
    SELECT A.C_ACCOUNT_NUMBER,
    A.C_RELATIONSHIP_NO,
    A.C_REFERENCE_CURREN,
    A.C_ACCT_CAT,
    A.C_CTL01,
    A.C_STATUS,
    A.C_ID
    FROM SB_F_CUSTODY_ACCOU A
    WHERE A.C_STATUS = 'ACT'
    AND (A.C_CTL01 <> 51
    OR ( A.C_CTL01 = 51 AND
    A.C_RELATIONSHIP_NO NOT IN
    (SELECT B.N_PORTFOLIO
    FROM SB_X_COUNTY_PARATE B
    WHERE B.C_CTL01 > 51
    AND B.C_STATUS = 'ACT')))
    ORDER BY A.C_CTL01, A.C_RELATIONSHIP_NO
    WITH UR


    N_PORTFOLIO is definied as nullable.

    Assuming that the subselect returns 4 valid B.N_PORTFOLIO values and 3 records with null values, the entire condition (OR A.C_CTL01 = 51....) is set to false so there are no A.C_CTL01 = 51 records in the output.

    However, looking at the actual data, there are lots of records that satisfy the condition
    (A.C_CTL01 = 51 AND A.C_RELATIONSHIP_NO NOT IN...)

    Is this something that DB2 is supposed to do? All conditions that contains a NULL record/value will be treated as false?

    I can do a workaround/fix by using IFNULL(B.N_PORTFOLIO,0) in the subselect but wanted to verify if the above scenario is by db2 design or is there a bug?

    Thanks,
    Paul

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have discovered sql's three-valued logic

    with WHERE x NOT IN (subquery), if any of the subquery results are NULL then the condition evaluates UNKNOWN

    example:

    ... WHERE x NOT IN ( 3, 4, NULL, 5 )

    this is equivalent to

    ... WHERE NOT (x IN (3, 4, NULL, 5))

    which is equivalent to

    ... WHERE ( (x<>3) and (x<>4) and (x<>NULL) and (x<>5) )

    which is equivalent to

    ... WHERE ( (x<>3) and (x<>4) and UNKNOWN and (x<>5) )

    which is equivalent to

    ... WHERE UNKNOWN

    and hence no rows selected

    to fix your problem, you should change
    Code:
    A.C_RELATIONSHIP_NO 
      NOT IN (
        SELECT B.N_PORTFOLIO 
          FROM SB_X_COUNTY_PARATE B 
         WHERE B.C_CTL01 > 51 
           AND B.C_STATUS = 'ACT'
             )
    to
    Code:
    NOT EXISTS (
        SELECT *
          FROM SB_X_COUNTY_PARATE B 
         WHERE B.N_PORTFOLIO 
             = A.C_RELATIONSHIP_NO
           and B.C_CTL01 > 51 
           AND B.C_STATUS = 'ACT'
             )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    28

    Talking

    Thanks!!! The suggested change works!!

    I didn't know about that....why would (x<>NULL) be an UNKNOWN? Does it mean that DB2 can only process NULL values if you specify IS(NOT) NULL in the condition?
    BTW...I added B.N_PORTFOLIO IS NOT NULL in the subselect and it also worked. =)

    ***
    I sent an email to ibm helpdesk regarding this and it looks like they took a different approach to resolving my question...They suggested changing the subselect sql to negative conditions..
    OR ( A.C_CTL01 = 51 AND
    A.C_RELATIONSHIP_NO IN
    (SELECT B.N_PORTFOLIO
    FROM SB_X_COUNTY_PARATE B
    WHERE B.C_CTL01 <= 51
    AND B.C_STATUS NOT <> 'ACT')))

    This didn't work.
    ***

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by psumali
    Does it mean that DB2 can only process NULL values if you specify IS(NOT) NULL in the condition?
    yes, and this is also the case in all other databases that have NULL

    interesting suggestion by ibm helpdesk, but clearly wrong

    if your intention was this --

    OR ( A.C_CTL01 = 51 AND
    A.C_RELATIONSHIP_NO NOT IN
    (SELECT B.N_PORTFOLIO
    FROM SB_X_COUNTY_PARATE B
    WHERE B.C_CTL01 > 51
    AND B.C_STATUS = 'ACT')))

    then their suggestion --

    OR ( A.C_CTL01 = 51 AND
    A.C_RELATIONSHIP_NO IN
    (SELECT B.N_PORTFOLIO
    FROM SB_X_COUNTY_PARATE B
    WHERE B.C_CTL01 <= 51
    AND B.C_STATUS NOT <> 'ACT')))

    is wrong on several levels, not the least of which is the extremely curious (and incorrect) comparison operator "NOT <>" on the last line

    NOT IN (something) is not necessarily logically equivalent to IN (the reverse)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    One other "trick" is to use the COALESCE function - which returns the first non-null argument, or null if they are all null. Providing a non-null constant argument means that the function will never return a null.

    In your situation, you might have used something like
    ...
    A.C_RELATIONSHIP_NO NOT IN
    (SELECT coalesce(B.N_PORTFOLIO,-99)
    FROM SB_X_COUNTY_PARATE B
    ...


    James Campbell

Posting Permissions

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