Re: Round up a number to 4 decimal positions SQL DECFLOAT QUANTIZE

On 19 Mar 2013 13:30, Jeff Young wrote:

The requirement is to take a currency conversion rate and convert it
to a price value.
The rate in question is 70.758.
The calculation is 1 / Rate (70.758) which yields .0141326.
I need the result to be .0142, not .0141.

When reading the SQL reference, I came across the QUANTIZE function.
When the <ed: following two statements are issued consecutively>
in the interactive SQL environment, I get the desired result:

Set CURRENT DECFLOAT ROUNDING MODE = Round_Up
select quantize((1/70.758),.0001) FROM SYSIBM/SYSDUMMY1

When I put the statements in my program it appears to ignore the SET
value.

How can I get this to work in my RPG program?
The embedded SQL is:

Exec SQL
Set CURRENT DECFLOAT ROUNDING MODE = Round_Up;
Exec SQL
Select Quantize(:Work_Price,.0001) Into :Sell_Price
from SysIbm/SysDummy1;

Presumably the effect was the defaulted to ROUND_HALF_EVEN defined as "Round to nearest digit. If equidistant, round to the nearest even digit.", but that would not be obvious with the value being used.

Was the SQLCODE from the SET checked for an error? I doubt that there would be a difference between embedded and dynamic, but perhaps the upper-cased token ROUND_UP as shown in the docs vs the mixed-case value Round_Up shown in both the dynamic and embedded examples. Because the value specified can be a string-constant, there is a possibility that one interface folds the specified value while another does not; although I would expect that to be considered a defect.

FWiW, a couple other observations:

The STRSQL result is a DECFLOAT, but the embedded example presumably has the variable SELL_PRICE defined as something like 15P04 into which the DecFloat is cast. So a better equivalency for the interactive SQL request would be the following to cast the result of the expression [where the 15 and 4 were replaced with the same precision and scale as the variable SELL_PRICE]:
select dec(quantize((1/70.758),.0001), 15, 4) FROM SYSIBM/SYSDUMMY1

The interactive SQL request uses an expression INT(1)/DEC(70.758, 5, 3) as the first argument of QUANTIZE, but the embedded request uses just WORK_PRICE which has no declaration shown. A better comparison would have both using like-values\declarations and like-expressions.