Results 1 to 4 of 4

Thread: simple query

  1. #1
    Join Date
    Sep 2003
    Posts
    218

    Unanswered: simple query

    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,

  2. #2
    Join Date
    Jun 2004
    Posts
    57

    Is It Ok?

    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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.

Posting Permissions

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