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.
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.