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

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
to

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.
***

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)

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