Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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

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

- 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

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

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

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

*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

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

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

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

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

Thanks Dnoeth !!

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.