Hi,

I am using db2 ese v8.1.4a on win2k platform and I have the following question:

My select query looks some thing like this...

SELECT SUM(A.Col1 + B.Col1), SUM(A.Col1 - Col1)
FROM TableA A, TableB B
WHERE A.ID = B.ID

Now the issue is, that how to exclude NULL values from the tables so that they don't appear in resultant.

Any help is appreciated.

Thanks,

If I understand corectly, I think you can use something like that

SELECT * FROM
(
SELECT A.id, SUM(A.a + B.b) as FIRST_, SUM(A.a - B.b) AS SECOND_
FROM A, B
WHERE A.id = B.id
GROUP BY A.id
) AS RESULT
WHERE RESULT.FIRST_ IS NOT NULL AND RESULT.SECOND_ IS NOT NULL

SELECT SUM(A.Col1 + B.Col1), SUM(A.Col1 - B.Col1)
FROM TableA A, TableB B
WHERE A.ID = B.ID
AND A.Col1 IS NOT NULL
AND B.Col1 IS NOT NULL

Do you want nulls treated as zeroes? (1 + null = 1) or do you want the rows with nulls completely ignored? (1 + null is excluded)

If you want to include the rows with nulls in the calculations, then use the COALESE or VALUES function to convert nulls to zero.