Database

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2015
06:36 AM

09-18-2015
06:36 AM

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 11

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2015
10:35 AM

09-18-2015
10:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-18-2015
12:05 PM

09-18-2015
12:05 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-20-2015
11:23 PM

09-20-2015
11:23 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-20-2015
11:49 PM

09-20-2015
11:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2015
02:10 AM

09-21-2015
02:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2015
04:03 AM

09-21-2015
04:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2015
07:06 AM

09-21-2015
07:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2015
07:14 AM

09-21-2015
07:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-21-2015
07:27 AM

09-21-2015
07:27 AM

Re: Numeric overflow for decimal (38,4)

Thanks Dnoeth !!