Turn on suggestions

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

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- SAS teradata upload error

Options

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

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 07-09-2019 08:01 AM
(3302 views)

Hi

I'm trying to upload data to Teradata using:

**proc** **sql**;

create table v4 as

select

EXTRACTION_DATE format=DATE9.,

LIMIT as LIMIT_AMT format=best32.

from work.x

quit;

proc sql;

insert into tera.y

select * from v4;

quit;

I get the error:

ERROR: On an insert, update, or index operation, the value applied to a Teradata 8 byte decimal was out of range - greater than

9.223372036854775808E18.

How do I solve this?

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

10 REPLIES 10

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Can you provide some additional information? Some dummy data that shows us what some values would look like on the work.x table, what they look like on the v4 table, the full log from the creation of the v4 table to just after the error message, etc.

Thanks

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi

More information

The limit_amt variable in Teradata is defined as

LIMIT_AMT DECIMAL(32,9) DEFAULT 0.000000000 COMPRESS ,

The limit that I'm trying to upload is

10917118759

The format in SAS is

NAME | TYPE | LENGTH | VARNUM | LABEL | FORMAT | FORMATL | FORMATD | INFORMAT | INFORML |

LIMIT_AMT | 1 | 8 | 7 | BEST | 16 | 2 | BEST | 32 |

Lastly - the dataset on Teradata has been predefined and I don't have access to alter the definitions there

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

One last question, what are the formats for these two fields on the already existing tera.y table? Can you provide dummy examples for that data as well?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Or more specifically, what is data type and precision in the TD table?

Data never sleeps

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Show the definition of the table you are loading into. From the Teradata documentation an 8 byte decimal value is large enough for decimal numbers with a wide of 10 to 18 digits. The number in your error message has 19 digits. Note also that when you define the variable in Teradata you need to specify how many of those digits are to the right of the decimal point, so the upper bound could be even lower.

Also SAS cannot store 19 digits exactly as a number since it uses 8 byte floating point binary numbers. The maximum number of decimal digits is 15. So if I read in

9,223,372,036,854,775,808

and print it back out it becomes

9,223,372,036,854,777,856

Notice how the last four digits have changed.

What do those number represent? Are they actual numbers that you want to compare? Do you really care about the 16-19 th least significant decimal digits?

Perhaps you just need to create the field as a floating point number in Teradata instead of a decimal number.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

After further investigation my question now is:

How do I upload a value gt 999,999,999.9 to Teradata where the field is decimal(32,9)?

I either get the

ERROR: On an insert, update, or index operation, the value applied to a Teradata 8 byte decimal was out of range - greater than

9.223372036854775808E18.

or

ERROR: Teradata insert: Numeric overflow occurred during computation.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Teradata does not want more than 9 decimal places. You told SAS it could format the number with up to 30 decimal places.

Try telling SAS to format the number with no more than 9 decimal places.

Try chaning the format attached to the variable from BEST32. to 32.9 and see if it works.

You are still not going to be able to get SAS to store more than 15 decimal digits exactly

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks, Tom

Changing formats on the SAS side does not seem to work

It seems that the number is too large, any value less than or equal to 999,999,999.9 gets inserted into the Teradata, any number larger and I get the error

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi Tom,

I ran into a similar issue with an error saying "On an insert, update, or index operation, the value applied to a Teradata integer was out of range - greater than 2147483647."

I ran the query couple times before but I didn't get any error. Double check all of my integer value but none of them exceed 2147483647. Could you please advise what would cause an error? Thanks much.

I ran into a similar issue with an error saying "On an insert, update, or index operation, the value applied to a Teradata integer was out of range - greater than 2147483647."

I ran the query couple times before but I didn't get any error. Double check all of my integer value but none of them exceed 2147483647. Could you please advise what would cause an error? Thanks much.

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. **Registration is now open through August 30th**. Visit the SAS Hackathon homepage.

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.