I am trying to cast a column into decimal but its failing with the error
sel cast(a25 as decimal(38,2)) from TempUpdate
SELECT Failed. 2617: Overflow occurred computing an expression involving TempUpdate.a25
So it means the column is having larger value than an decimal can hold rite?
Having said that the below statement runs fine
sel cast(cast(a25 as varchar(100)) as decimal(38,2)) from TempUpdate
so is casting to number and integer
sel cast(a25 as integer) from TempUpdate;
sel cast(a25 as number) from TempUpdate;
Can you guys pls giv u r thoughts on whats going wrong??
The datatype for the column is output of an expression (col1*col2* and sumthing lyk dis).
The result has almost 55Mill distinct values, so i am not sure where it is failing and still if u want i can post some sample data?
Also i checked the max and min in the resultset it is well within the range of decimal(38,2); Also i casted them individually and it works fine too!
I am not sure whehter it will help or not : When i tried to create a volatile table with "with data" and this select statement inside, The table is being created with Number Datatype.
This is the complete expression
sel col1 + POWER( b.col2 , 2 ) * c.col3 AS a25
here col1 & col2 are decimal (12,0) and col3 is integer.
What baffles me is when i tried to cast as decimal i am getting that error whereas i casted it as varchar(here no data loss is possible because i casted it to varchar(100)) and then cast it as decimal its not throwing the error.
You might open an incident with Teradata support, the possible range of values the result should fit into DEC(38,2), at least it worked when i just tried it.
If there were only decimals i would assume a MAXDECIMAL set to less than 38 in dbscontrol, but the resulting datatye of a POWER is NUMBER. Do you get the same error when you change POWER to b.col2**2 (which results in float)?
Are any rows returned when you add a WHERE a25 <> cast(cast(a25 as varchar(100)) as dec(38,2)))?
First i tried WHERE a25 <> cast(cast(a25 as varchar(100)) as dec(38,2)) and got so many rows as mismatch. And now i am more confused than ever. All the 0.00 columns have been converted to some random value (each 0.00 is converted into different random value - If sum1 has said to me dis s wat happening i wud hav laughed at them but believe me this s wat happening). I will try the remaining solution given by you later.
This s de query i tried
SEL a25,CAST(a25 AS VARCHAR(41)),CAST(CAST(a25 AS VARCHAR(100)) AS DECIMAL(38,2))
I am putting first ten rows here
1 0.00 2.159999999999999946 2.16
2 1.00 1 1.00
3 0.00 9.9200000000000018 9.92
4 0.00 2.159999999999999946 2.16
5 0.00 1.339999999999999944 1.34
6 0.00 1.339999999999999944 1.34
7 1.00 1 1.00
8 0.00 14.919999999999999942 14.92
9 0.00 2.159999999999999946 2.16
10 1.00 1 1.00
Also while trying the below query
SEL a25,CAST(a25 AS VARCHAR(100)),CAST(CAST(a25 AS VARCHAR(100)) AS DECIMAL(38,2))
WHERE CAST(CAST(a25 AS VARCHAR(100)) AS DECIMAL(38,2)) <> a25
am getting some results in which both the columns are having same value(atleast to ma eyes!) but still am getting.
0.10 .100000000000001 0.10
-0.04 -.0399999999999987 -0.04
0.04 .0399999999999988 0.04
What am i missing here, i am completely lost?
What patch level are you running?
There have been a few Tech Alerts regarding NUMBERs and calculation/cast/rounding, you probably run into one of them. Most are fixed, but this is open:
NT3194 Rounding error with casting NUMBER(*) to CHAR/VARCHAR
You might try to avoid the NUMBER by replacing the POWER(b.col2,2) with CAST(b.col2 as DEC(38,2)) * b.col2
Asusual Thanks Dieter for u r timely replies :)
This is the DB release info.
LANGUAGE SUPPORT MODE Standard.
Where can we get all the information about the bugs like the one you mentioned any mailing list or URL?
I will try what u have suggested and post the results later.