BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ZeroSeven
Calcite | Level 5

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
Tom
Super User Tom
Super User

Upload to a volatile table where the variable is defined as float and then copy from there into the real target using a passthru query.

View solution in original post

10 REPLIES 10
tsap
Pyrite | Level 9
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
ZeroSeven
Calcite | Level 5

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

NAMETYPELENGTHVARNUMLABELFORMATFORMATLFORMATDINFORMATINFORML
LIMIT_AMT187 BEST162BEST32

 

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

tsap
Pyrite | Level 9
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?
LinusH
Tourmaline | Level 20
Or more specifically, what is data type and precision in the TD table?
Data never sleeps
Tom
Super User Tom
Super User

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.

ZeroSeven
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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

ZeroSeven
Calcite | Level 5

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

Tom
Super User Tom
Super User

Upload to a volatile table where the variable is defined as float and then copy from there into the real target using a passthru query.

MelissaN
Obsidian | Level 7
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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 10 replies
  • 3807 views
  • 0 likes
  • 5 in conversation