cancel
Showing results for
Did you mean:

# Rounding when casting to decimal

SQL-Fighter

Hi,

I'm seeing some slightly odd behaviour when casting to decimal. Please see the below example. Why is it that if we provide an explicit figure it rounds and then truncates whereas if we provide and expression it decides to simply truncate?

``````select cast(99.05 as decimal(10,1)) as Ans1
,cast(5943.00/60.00 as decimal(10,1)) as Ans2``````

Result

 Ans1 Ans2 99.1 99.0
1 ACCEPTED SOLUTION
Xpert

Hi,

I´d say it´s rooted in the fact that your first input is a decimal, while the type of your second input ( the expression 5943.00/60.00  is double ).

8 REPLIES 8
Xpert

and one final one from me:

Xpert

Hi,

I´d say it´s rooted in the fact that your first input is a decimal, while the type of your second input ( the expression 5943.00/60.00  is double ).

SQL-Fighter

I've just found these resources that mention a slight issue with DOUBLE rounding.

https://community.exasol.com/t5/database-features/double-values/ta-p/213

https://www.exasol.com/support/browse/SOL-102

Example.

``````create or replace view my_schema.DataTypes
as
select ROUND(    ((71222-65504)/65504*100)                  ,1) as approximate
,ROUND(CAST((71222-65504)/65504*100 AS DECIMAL(16,3)) ,1) as exact``````

Resultset 1

 Approximate Exact 8.7 8.7

Resultset 2

 COLUMN_NAME SQL_TYPE Approximate DOUBLE Exact DECIMAL(15,1)
SQL-Fighter

Thanks for your input, it really helped demystify what's happening behind the scenes. It looks to be the divisor operator is returning double return value...
There is no way to retain that behaviour of round then truncate when using decimal values in a arithmetical calculation as below.

``````create or replace view my_schema.DataTypes
as
select cast(99.05 as decimal(10,1)) as Ans1
,cast(5943.00/60.00 as decimal(10,1)) as Ans2
,5943.00/60.00 as Ans3
,cast(5943.00 as decimal(10,2))/60.00 as Ans4
,5943.00/cast(60.00 as decimal(10,2)) as Ans5
,cast(5943.00 as decimal(10,2))/cast(60.00 as decimal(10,2)) as Ans6
,cast(cast(5943.00 as decimal(10,2))/cast(60.00 as decimal(10,2)) as decimal(10,1)) as Ans7``````

gives the following

``````select *
from my_schema.DATATYPES;

desc my_schema.DATATYPES``````

Resultset 1

 Ans1 Ans2 Ans3 Ans4 Ans5 Ans6 Ans7 99.1 99 99.05 99.05 99.05 99.05 99

Resultset 2

 COLUMN_NAME SQL_TYPE Ans1 DECIMAL(10,1) Ans2 DECIMAL(10,1) Ans3 DOUBLE Ans4 DOUBLE Ans5 DOUBLE Ans6 DOUBLE Ans7 DECIMAL(10,1)
Xpert

what about cast(5943.00/cast(60.00 as decimal(10,2)) as decimal(10,2)) as Ans8 ?

it´ll yield a decimal(10,2) type with a result of 99.05 , which is what you wanted - right ?

SQL-Fighter

I just read this on the following doc link https://docs.exasol.com/sql_references/data_types/datatypedetails.htm

"Numeric Data Types

Exasol supports approximately and exact numerical data types. The difference when using the approximate data types arises from the possible rounding differences, which can occur due to the type of storage and the way computations are performed. Information losses of this nature do not occur when using exact numeric types because of the type of storage"

This would explain the random rounding behaviour.

It has just tripped me a little as to why Exasol interprets the 99.05 as decimal but then the 5943.00/60.00 result as double [precision] (floating point).

I've tested this with the following and it's still dumbfounded me. Why the difference in interpretation between the last two; y and z?

``````create or replace view my_schema.DataTypes
as
select 100 as w
,100.00 as x
,9905.00/100.00 as y
,5943.00/60.00 as z``````

Resultset 1

 w x y z 100 100 99.05 99.05

Resultset 2

 COLUMN_NAME SQL_TYPE w DECIMAL(3,0) x DECIMAL(3,0) y DECIMAL(4,2) z DOUBLE

SQL-Fighter

I was after the result '99.1' after it having been rounded up to 1 decimal place. Key is to use the explicit round function before casting.

``````create or replace view my_schema.DataTypes
as
select cast(5943.00/cast(60.00 as decimal(10,2)) as decimal(10,2)) as Ans8
,round(5943.00/60.00, 1) as Ans9
,cast(round(5943.00/60.00, 1)  as decimal(10,1)) as Ans10``````

So it would yield the following results

``````from my_schema.DATATYPES;

desc my_schema.DATATYPES``````

Resultset 1

 Ans8 Ans9 Ans6 99.05 99.1 99.1

Result 2

 COLUMN_NAME SQL_TYPE Ans8 DECIMAL(10,2) Ans9 DOUBLE Ans10 DECIMAL(10,1)
Xpert