Error while casting to decimal!

Database
Enthusiast

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

9 REPLIES
Senior Apprentice

Re: Error while casting to decimal!

What is the datatype/format of a25?

Can you show some data?

Dieter

Enthusiast

Re: Error while casting to decimal!

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.

Re: Error while casting to decimal!

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

Enthusiast

Re: Error while casting to decimal!

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.

Senior Apprentice

Re: Error while casting to decimal!

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

Enthusiast

Re: Error while casting to decimal!

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?

Senior Apprentice

Re: Error while casting to decimal!

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

Enthusiast

Re: Error while casting to decimal!

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.

Senior Apprentice

Re: Error while casting to decimal!

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

Dieter