I am trying to generate a report from Sybase IQ(new to me) and need to convert the amounts field as per user requirement. Current report is generated by Oracle Database and I need to replicate same exact report from Sybase IQ as well along with rounding of Amounts field.
Below are some example of amounts and its expected value.
| Value from Sybase query | Expected value after conversion |
|---|---|
| -433.39999999999998 | -433.4 |
| -681250.0 | -681250 |
| -896.38999999999999 | -896.39 |
| -26152.060000000001 | -26152.06 |
I have tried some conversions on my own but they are not giving exact results as expected the only near I got is with CAST to decimal datatype.
select round(convert(decimal(23, 2), amt_field), 2))) from iq_table
select cast(round(amt_field,2) as decimal(23,2)) from iq_table
select TRUNCNUM( amt_field, 2 ) from iq_table
select round(amt_field,2) from iq_table
select cast(amt_field as decimal(23,3)) from iq_table
Please help if there are any other ways using which the above scenarios can be handled in Sybase IQ.