Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    30

    Unanswered: show zero results instead of not showing any data

    Hello,

    I am using MS Access 2010 I have a pass thru query getting data from db2 no issues here, my problem is if the results are zero I want to show (see below for code) the month field (SHSHPM) = 4 and the year (SHSHPY) = 2014 and the COST field would equal zero amount. If I ran the pass thru I get nothing, which is correct. Does anyone know how I can do that? Thank you in advance.

    SELECT DISTINCT
    ASODTANNA.ASOSHHM.SHSHPM, ASODTANNA.ASOSHHM.SHSHPY, ASODTANNA.ASOSHHM.SHTXCS AS State,
    SUM(CASE WHEN ASODTANNA.ASOSHDM.SDSHPQ * AICDTANNA.AICITMM.ITAVGC IS NULL THEN 0 ELSE ASODTANNA.ASOSHDM.SDSHPQ * AICDTANNA.AICITMM.ITAVGC END) AS Cost
    FROM ASODTANNA.ASOSHHM, ASODTANNA.ASOSHDM, AICDTANNA.AICITMM
    WHERE ASODTANNA.ASOSHHM.SHSHPM = ASODTANNA.ASOSHDM.SDSHPM AND ASODTANNA.ASOSHHM.SHSHPY = ASODTANNA.ASOSHDM.SDSHPY AND
    ASODTANNA.ASOSHHM.SHSON = ASODTANNA.ASOSHDM.SDSON AND ASODTANNA.ASOSHHM.SHSHPN = ASODTANNA.ASOSHDM.SDSHPN AND
    ASODTANNA.ASOSHDM.SDICH = AICDTANNA.AICITMM.ITITM AND (ASODTANNA.ASOSHHM.SHPSTF = 'Y') AND (ASODTANNA.ASOSHHM.SHSHPM = '04') AND (ASODTANNA.ASOSHHM.SHSHPY = '2014') AND
    (ASODTANNA.ASOSHHM.SHTXCS = 'CA') AND (ASODTANNA.ASOSHDM.SDUNTR = 0) AND (ASODTANNA.ASOSHDM.SDSHPQ > 0) AND (AICDTANNA.AICITMM.ITITM IN ('05119', '05121', '05122', '05123', '05124',
    '05125', '05127', '05128', '05130', '05131', '05132', '05134', '05135', '05136', '05137', '05141', '05144', '05393', '05886', '06712'))
    GROUP BY ASODTANNA.ASOSHHM.SHSHPM, ASODTANNA.ASOSHHM.SHSHPY, ASODTANNA.ASOSHHM.SHTXCS, ASODTANNA.ASOSHDM.SDUNTR, AICDTANNA.AICITMM.ITITM, ASODTANNA.ASOSHDM.SDSHPQ

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If a query returns a Null value in a column, you can have it to return a zero (0) instead using the Nz() function:
    Code:
    SELECT Nz(Column) FROM Table...
    - If Column is not Null --> returns the value of Column.
    - If Column is Null --> returns 0.

    In T-SQL and in a pass-through query, you would use:
    Code:
    SELECT IsNull(Column, 0) FROM Table...
    I don't now the syntax used in DB2, so I can't say whether the expression
    Code:
    CASE WHEN ... IS NULL THEN... ELSE...
    is equivalent.
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    30
    Thank you Sinndho you gave me some good idea. I really appreciate your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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