You can translate the question and the replies:

Decimal precision issue with division in Denodo with Teradata as data source

Hi Im encountering issues with decimal precision while performing division in denodo, my data souce is Teradata Heres the scenario: In Teradata when I have values like 16,968 and divide the number by 100. Teradata is returning the result by rounding off and giving 170. however , when performing the same operation in Denodo Im getting 169.68. Additionally for numbers like 96.5 Teradata rounds off to 96 while denodo retains the decimal precision (96.5) I have used round function in denodo to address the issues, but the problem reamins the same. Could you provide guidance to resolve this issues? Thanks
user
20-02-2024 00:08:03 -0500
code

1 Answer

Hi, It's my understanding that you get undesired precision results when trying to apply division over numbers. I would recommend first leaving the database out, simply seeing if you can cast and model the output as you need only using Denodo VQL syntax. Then you can try connecting to the Database datasource for it with integer or float/double values accordingly. For example, a snippet like this run in VQL Shell will show you a few different results: ``` with raw_data as (select 16968.0 as value_float,16968 as value_integer union select 23456.0 as value_float,23456 as value_integer), a_few_basic_ops as ( select value_integer as original_integer_value, value_float as original_float_value, value_integer/100 as integer_divided_value, value_float/100 as float_divided_value, cast('float',value_integer)/100 as integer_cast_then_divide_value from raw_data) select * , cast('int',integer_cast_then_divide_value) as recasted_as_integer, round(integer_cast_then_divide_value) as rounded_casted, round(float_divided_value) as rounded_float from a_few_basic_ops ; ``` With this, you see that the core of getting the right value is by using the [CAST](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/conversion_functions/conversion_functions#cast) operator. Once the right casting is in place, [ROUND](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/numeric_functions/numeric_functions#round) operator also works fine. After that, if I replace the subquery raw_data with the real database, with one field as integer, and another one as float, I still get the same results. Additionally, there is [a related question about ROUND](https://community.denodo.com/answers/question/details?questionId=9060g0000004EwzAAE&title=Rounding+2+decimals+not+working+as+expected) in regards to forcing decimal digits that might offer more hints. Hope this helps.
Denodo Team
21-02-2024 10:40:01 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here