I extracted the SQL of a "summary only" WRKQRY using RTVQMQRY. The query has "Use Rounding" set to Y. I've done this before, and with some tweaking and using aggregate functions, have been able to replicate queries in SQL accurately. In this case, however, I kept running into errors when trying to run my SQL code--I discovered that one of the fields, regardless of the length it was prescribed to be in the wrkqry, was giving a result that was filled out with trailing 0's (i.e. 0.08 became 0.08000000000...etc with about 25 trailing zeros). Any subsequent calculations with that result caused overflow issues, translating the result into all plus signs, etc.
ROUND() didn't seem to have any impact; using it on the calculated result field resulted in the same trailing 0's, even though neither of the elements in the calculation nor the result field they were placed in were of that length. I worked around the issue by wrapping the troublesome field in DECIMAL() and limiting it to the length of the field indicated in WRKQRY's field definition. This works, and the data matches now...almost completely. There are very small differences (like .01) in a couple records, which leads me to believe that WRKQRY does some rounding or truncating somewhere in the process of its calculations that I can't peg down. Does anyone have any insight to offer here on what WRKQRY may be doing under the hood that could clarify this for me? Many thanks!
ETA: I'm wondering if there's a distinction at play here between floating point and decimal numbers...
WRKQRY's rounding process
Re: WRKQRY's rounding process
For anyone who's interested, I think I've figured out the solution to the above and similar problems. I've had to wise up about a few SQL quirks and also the WRKQRY rounding process, but I think I've gotten there.
The problem stemmed from having calculated fields that have "sub-calculations" within them, so like:
...where FLD01, FLD02, and the desired output/field to hold the result are of three different precisions. It seems to me that "Use Rounding" will round to the highest precision of each "sub-calculation", and then round the final result field to that result field's precision. So in the above, if FLD02 has a precision of 3, you'd round FLD02/100 to a precision of 3. If the final field result has a precision of 2, even if FLD01 has a precision of 4, you'd round the overall calculation to 2. So there are kind of "waves" of rounding that take place at each calculated step.
Another aspect to this was division within SQL, which causes all sorts of headaches (defaults to integer division, things need to be cast, etc. This is probably the source of my "lots of 0's" issue before). I worked around this, and (imo) made the query easier to decipher by multiplying by the inverse in place of the division. While I have read some articles that these aren't the exact same calculation, it seems that any differences are way deeper in precision than all of my rounding goes to anyway, so my data is tying up well. I welcome any feedback on that, however!
The problem stemmed from having calculated fields that have "sub-calculations" within them, so like:
Code: Select all
FLD01 * (FLD02 / 100)Another aspect to this was division within SQL, which causes all sorts of headaches (defaults to integer division, things need to be cast, etc. This is probably the source of my "lots of 0's" issue before). I worked around this, and (imo) made the query easier to decipher by multiplying by the inverse in place of the division. While I have read some articles that these aren't the exact same calculation, it seems that any differences are way deeper in precision than all of my rounding goes to anyway, so my data is tying up well. I welcome any feedback on that, however!