All Forums Database
barani_sachin 141 posts Joined 01/12
12 Apr 2013
Error while casting to decimal!

Hi All,
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??

dnoeth 4628 posts Joined 11/04
13 Apr 2013

What is the datatype/format of a25?
Can you show some data?
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
14 Apr 2013

Dieter,
    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.

Adithya 3 posts Joined 08/07
15 Apr 2013

Can you tell the datatype of col1,col2 and other columns that are used to calculate a25.

Regards,
Adithya

barani_sachin 141 posts Joined 01/12
15 Apr 2013

Adithya,
    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.

dnoeth 4628 posts Joined 11/04
15 Apr 2013

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)))?
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
16 Apr 2013

Dieter,
    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)) 
FROM TempUpdate 

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)) 
FROM TempUpdate
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?

dnoeth 4628 posts Joined 11/04
16 Apr 2013

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
Dieter

Dieter

barani_sachin 141 posts Joined 01/12
16 Apr 2013

Asusual Thanks Dieter for u r timely replies :)
This is the DB release info.
RELEASE 14.00.03.01
VERSION 14.00.03.02
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.
 

dnoeth 4628 posts Joined 11/04
16 Apr 2013

Ask your DBA, he should have access to the Tech Alerts etc. using Teradata@YourService.
Dieter

Dieter

You must sign in to leave a comment.