Numeric overflow for decimal (38,4)

Database
Enthusiast

Numeric overflow for decimal (38,4)

Hi gurus,

I am on Teradata version 15 

Teradata allow maximum decimal field size is 38

I have a scinario where the data in one decimal column (decimal  (38,4)) is exceedding the teradata limit .

I have four columns all are (decimal(38,4)) we are using this four columns to do caliculations (Product of all four columns ) and when we are trying to do this the result is exceeding teradata limit and throing error 2616 but the requirement is to store the values to such a presicion for reporting needs .

can any one suggest me on how to achive this with out impacting the performance or changing the end column definition as this is in production with huge data .

Quick help needed please.

Thanks in advance .

11 REPLIES
Enthusiast

Re: Numeric overflow for decimal (38,4)

Here is the sample Code 

select case when character_length( cast ((cast(3571893687 as decimal (38,0))*cast(3566028423 as decimal(38,
0)) * cast(3558464912 as decimal(38,0)) * cast(13056923 as decimal(38,
0))) as varchar(38)))-1 <=38 then (cast(3571893687 as decimal (38,0))*cast(3566028423 as decimal(38,
0)) * cast(3558464912 as decimal(38,0)) * cast(13056923 as decimal(38,
0))) else (cast(3571893687 as decimal (38,0))*cast(3566028423 as decimal(38,
0)) * cast(3558464912 as decimal(38,0)) * cast(13056923 as decimal(38,
0)))/10 end

 different numeric represent different columns .

character_length( cast ((cast(3571893687 as decimal (38,0))*cast(3566028423 as decimal(38,
0)) * cast(3558464912 as decimal(38,0)) * cast(13056923 as decimal(38,
0))) as varchar(38)))-1 <=38

need help in identifying  the product of the four columns is >38 and then moue to else part .

Quick help is much appriciated 

thanks in advance

Enthusiast

Re: Numeric overflow for decimal (38,4)

Is your actual data type is DECIMAL(38,4) or DECIMAL (38,0) ? 

Also can you please give the Max values in those 4 columns?

Enthusiast

Re: Numeric overflow for decimal (38,4)

the data type is decimal (38,4)

and the max value is 

col1:3571893687 

col2:3566028423 

col3:3558464912 

col4:13056923 

Junior Contributor

Re: Numeric overflow for decimal (38,4)

sel 3571893687 * 3566028423 * 3558464912 * 13056923 

results in

591,816,208,590,977,000,480,673,972,272,291,376

which is 36 digits as integer part, clearly above the 34 digits of a DEC(38,4).

Without changing the datatype to DEC(38,2), you will not be able to store that result.

Another solution would be a NUMBER column:

sel cast(3571893687 AS number) * 3566028423 * 3558464912 * 13056923


Fan

Re: Numeric overflow for decimal (38,4)

hi dnoeth,

I am trying to display only last week order date from my table 

I am not allow to use HARD CODED DATES 

Junior Contributor

Re: Numeric overflow for decimal (38,4)

Please post a new question as a new topic.

There's a NEXT_DAY function, which returns the "next" weekday, e.g. for weeks from monday to sunday:

next_day(dat -14, 'mon') as week_begin
next_day(dat -8, 'sun') as week_end
Enthusiast

Re: Numeric overflow for decimal (38,4)

Also, Below queries could help you identifying the records that would fit in 38,4

--- When the product of 4 columns goes beyond 38,4

SEL CAST ( CAST ( CAST(3571893687 AS DECIMAL (38,4))* CAST(3566028423 AS DECIMAL(38,4)) AS DECIMAL ( 38,4) ) * CAST( CAST(3558464912 AS DECIMAL(38,4)) * CAST(13056923 AS DECIMAL(38,4)) AS DECIMAL (38,4) ) AS DECIMAL(38,2)) AS VAL
, INDEX ( CAST(VAL AS VARCHAR(38)), '.') AS DECIMAL_POSITION
, CASE WHEN DECIMAL_POSITION < 37 THEN ' THE VALUES WILL FIT IN DECIMAL ( 38, 4 )' ELSE ' NUMERIC OVERFLOW WILL OCCUR' END AS DATA_FIT
, VAL

--- When the product of 4 columns meets 38,4

SEL CAST ( CAST ( CAST(35713687 AS DECIMAL (38,4))* CAST(3566028423 AS DECIMAL(38,4)) AS DECIMAL ( 38,4) ) * CAST( CAST(3558464912 AS DECIMAL(38,4)) * CAST(13056923 AS DECIMAL(38,4)) AS DECIMAL (38,4) ) AS DECIMAL(38,2)) AS VAL
, INDEX ( CAST(VAL AS VARCHAR(38)), '.') AS DECIMAL_POSITION
, CASE WHEN DECIMAL_POSITION < 37 THEN ' THE VALUES WILL FIT IN DECIMAL ( 38, 4 )' ELSE ' NUMERIC OVERFLOW WILL OCCUR' END AS DATA_FIT
, VAL
Junior Contributor

Re: Numeric overflow for decimal (38,4)

You get the number of digits before the decimal point easily using LOG instead of a CAST to VarChar + INDEX (which will not work correctly for negative values without adding ABS):

ceiling(log(abs(column)))
Enthusiast

Re: Numeric overflow for decimal (38,4)

Thanks Dnoeth !!