SQLRPGLE Calculations and Rounding

[TheZenbudda]

My default compile parameters for SQLRGPLE programs are set to DECFLTRND(*HALFEVEN). Even my IBM i SQL Scripts sessions are set to the same setting.

That being said, when I perform a division or multiplication calculation using sql, and I encase the calculation in a round() function, shouldn't the rounding rules use *HALFEVEN, or is that for DECFLOAT datatypes only?

My understanding of *HALFEVEN is, if the result field is a numeric(3,2) and the raw value is 1.146, then because the .14 is even, then it will not round the raw value to 1.15. However, it seems our system is always rounding. Then I thought maybe the *HALFEVEN rounding is being done without the round() function. But I am wrong on that as well.

What am I getting wrong with the SQL option to round using *HALFEVEN?

[jtaylor___]

I think *HALFEVEN only applies if the value ends in 5, so it wouldn't affect 1.146.

[JonBoy]

*HALFEVEN says that if the digit to be dropped is a 5 then the rounding action depends on the rightmost digit of the value. If it is even then the result is rounded down - if it is odd then it is rounded up.

So 12.345 becomes 12.34 because 4 is even. But 12.335 would also become 12.34 because 3 is odd.

[TheZenbudda]

Thanks for your responses. I think I see what you're saying, but my examples that I'm running do not support that logic (but maybe my examples are not accurate)

with rawvaluetable as (
    select 
        1.13 as rawvalue1
        , 1.14 as rawvalue2
    from 
        sysibm.sysdummy1
)
select
    rawvalue1
    , cast(round((rawvalue1 + .005),2) as numeric(3,2)) as roundedrawvalue1
    , rawvalue2
    , cast(round((rawvalue2 + .005),2) as numeric(3,2)) as roundedrawvalue2
from 
    rawvaluetable
; 

Based on what you're saying, i would have expected roundedrawvalue2 to stay at 1.14.

[jtaylor___]

I agree. I'd expect both 1.135 and 1.145 to round to 1.14.

[JonBoy]

I tested with this:

with rawvaluetable as (
    select
        1.135 as rawvalue1
        , 1.145 as rawvalue2
    from
        sysibm.sysdummy1
)
select
    rawvalue1
    , cast(round(rawvalue1,2) as numeric(3,2)) as roundedrawvalue1
    , rawvalue2
    , cast(round(rawvalue2,2) as numeric(3,2)) as roundedrawvalue2
from
    rawvaluetable
;

The 0.005 addition in the expression just complicates things.

It seems as if the setting is just being ignored completely. I used Run SQL scripts and changed the connection properties to a variety of different options including drop. Nothing made any difference so there's something odd going on - or else the docs just aren't explaining it properly.

I would suggest you open a ticket with IBM and see what they have to say. In the meantime I'lll see what I can find.

[JonBoy]

I'll come back with more detail later but I'm told that this is the result of the value being a float and therefore the rounding mode is not used and round half up is the default. You can use QUANTIZE to work round this.

[TheZenbudda]

thanks for your responses.

quantize() with a decfloat(.01) worked as expected.

I have another question. Do the "rounding defaults" that we are discussing here (ie. HALFEVEN, etc) only kick in when you specify round(), or are they the default rounding calculation when no round() is specified?