Midrange News for the IBM i Community


Posted by: renojim
mystery numbers appearing
has no ratings.
Published: 03 Sep 2015
Revised: 09 Sep 2015 - 652 days ago
Last viewed on: 21 Jun 2017 (1250 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