Midrange News for the IBM i Community


Posted by: renojim
mystery numbers appearing
has no ratings.
Published: 03 Sep 2015
Revised: 09 Sep 2015 - 567 days ago
Last viewed on: 29 Mar 2017 (1092 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)

I have the following statement embedded in RPG:

D fldone        s              4s 0
D fldtwo        s              7s 2
D fldthree     s               7s 2
D fldfour       s               7s 2

exec sql select                                    
  coalesce(sum(crs.fldone),0),                     
  coalesce(sum(mab.fldtwo),0),                      
  coalesce(sum(mab.fldthree),0),                     
  coalesce(sum(mab.fldfour),0)                      
     into                                          
      :totfldone,:totfldtwo,:totfldthree,:totfldfour     
    from fileone mab left outer join filetwo crs on
    dept = mabdept    and                         
    prod# = mabprod    and                         
    prodsub# = mabprodsub                               
    where                                          
    keyone = :myfirstkey   and                         
    keytwo = :mysecondkey;      

This statement works, except for totfldthree - it gets a 5 in the leftmost position, that comes out of nowhere, so if the correct result is 320.74, I'm getting 50320.74. The other fields have the correct values. I've checked the data, it's not complicated, it's supposed to be 320.74. I've copied the statement and removed the other fields from the select and into clauses, and I get the correct value, 320.74. I've done the join several different ways, all work, except for the same problem. I've set all the host variables in the into clause to 0 just prior to the statement. Can anybody take a stab at where the mystery 5 is coming from, or better yet, what to do about it? Yes, I could break it up into multiple statements, but that would be admitting defeat. And it would not be as pretty.

 

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: 1 years 6 months 27 days 14 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: 1 years 6 months 26 days 10 hours 23 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: 1 years 6 months 25 days 14 hours 58 minutes ago
Edited: Wed, 09 Sep, 2015 at 16:05:07 (567 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.