3 weeks ago - last edited 3 weeks ago
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 |
Solved! Go to Solution.
3 weeks ago
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 ).
3 weeks ago
and one final one from me:
3 weeks ago
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 ).
2 weeks ago
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
https://www.exasol.com/support/browse/EXASOL-1609 (https://www.exasol.com/support/secure/ReleaseNote.jspa?projectId=10090&version=14215)
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) |
2 weeks ago - last edited 2 weeks ago
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) |
2 weeks ago
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 ?
2 weeks ago
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 |
2 weeks ago
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) |
3 weeks ago
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In