Midrange News for the IBM i Community


Posted by: renojim
mystery numbers appearing
has no ratings.
Published: 03 Sep 2015
Revised: 09 Sep 2015 - 1190 days ago
Last viewed on: 11 Dec 2018 (1958 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

mystery numbers appearing Published by: renojim on 03 Sep 2015 view comments(3)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: mystery numbers appearing
Posted: 3 years 3 months 9 days 20 hours 21 minutes ago

You have a LEFT OUTER JOIN and I'm guessing you are getting NULLs returned or you wouldn't have the COALESCE. right?

Your SUM() functions are curious as you don't have a GROUP BY clause, but maybe that was just an omission?

Anyway, the SUM function will produce wierd results when some of the rows contain nulls.

Instead, put the COALESCE inside the SUM.

 SUM( coalesce(crs.fldone,0) )

That way as it is summing the rows, if crs.fldOne is null, it'll replace it with a zero value.

Posted by: renojim
Premium member *
Comment on: mystery numbers appearing
Posted: 3 years 3 months 9 days 6 hours 30 minutes ago

Thank you for the response.  So I moved the coalesce inside the sum and added group bys, even tried going back to an inner join - in this case, both will work - but still get exactly the same result. I took that field out and put it in it's own statement, which is identical except for the field list, and got the correct results. It's curious, and I would like to do it in one statement, but I have a long list of pgms to change and no time to do it, so I'm going to leave it at two statements and move on.

Thanks again.

 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: mystery numbers appearing
Posted: 3 years 3 months 8 days 11 hours 6 minutes ago
Edited: Wed, 09 Sep, 2015 at 16:05:07 (1190 days ago)

If you have SQL Query File, you could try running the statement with it, and the press F4 to view the record layout just to see what SQL is returning. That's what I do in situations like this.

RUNSQLF 'your sql statement'  <Enter>

Then when the result set is displayed, press F4 to view the result field definitions.