Database

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 .

11 REPLIES 11

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

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?

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

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

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

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_beginnext_day(dat -8, 'sun') as week_end`

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,4SEL  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`

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)))`

Thanks Dnoeth !!