topic Re: The value applied to a Teradata integer was out of range - greater than 2147483647 in SAS Enterprise Guide

The value applied to a Teradata integer was out of range - greater than 2147483647

MelissaN — Thu, 01 Aug 2019 13:09:06 GMT

Hi,

I ran a query in SAS to pull the data (63 columns, 18k rows) and then update this table to Teradata using ansi mode.

Create table APPS_TEMP as (select col1,col2,col3....col63 from abc)

libname Space teradata server='' user=&user_name password=&pwd schema='Space' mode=ansi;

insert into Space.APPS

select * from APPS_TEMP

I was able to run this with no issue last month, but this month I got this error message

"ERROR: On an insert, update, or index operation, the value applied to a Teradata integer was out of range - greater than 2147483647."

Please advise how I could fix it.

Any help is greatly appreciated.

Thanks

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

TomKari — Thu, 01 Aug 2019 22:34:55 GMT

Teradata probably uses a 4 byte integer format, and that is the largest number that can be contained within that format. Whereas SAS uses an 8 byte floating point format for numbers, and can store numbers with up to 15 digits.

If you need to store the number accurately in Teradata, you'll need to change the variable assignment in Teradata to either 8 byte integer (if that exists in Teradata), or eight byte floating point.

This will be a job for your database administrator, so start by talking to them.

Tom

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

MelissaN — Thu, 01 Aug 2019 22:45:45 GMT

Hi Tom,
Thanks for your response. None of my integer values are more than 15 digits. The max length is only 7 digits. I tried to do format 9 for my integer values, but it is still not working.

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

TomKari — Thu, 01 Aug 2019 23:36:20 GMT

Don't worry about formats; they don't change the underlying value of the variable.

Let's check the easiest thing first; run this code on your data. If there's a value that's not within limits, the error diagnostic will show you the line and the variable.

Tom

data _null_;
set abc;
if col1 > 2100000000 | col1 < -2100000000 then abort;
if col2 > 2100000000 | col2 < -2100000000 then abort;
if col3 > 2100000000 | col3 < -2100000000 then abort;
if col4 > 2100000000 | col4 < -2100000000 then abort;
if col5 > 2100000000 | col5 < -2100000000 then abort;
if col6 > 2100000000 | col6 < -2100000000 then abort;
if col7 > 2100000000 | col7 < -2100000000 then abort;
if col8 > 2100000000 | col8 < -2100000000 then abort;
if col9 > 2100000000 | col9 < -2100000000 then abort;
if col10 > 2100000000 | col10 < -2100000000 then abort;
if col11 > 2100000000 | col11 < -2100000000 then abort;
if col12 > 2100000000 | col12 < -2100000000 then abort;
if col13 > 2100000000 | col13 < -2100000000 then abort;
if col14 > 2100000000 | col14 < -2100000000 then abort;
if col15 > 2100000000 | col15 < -2100000000 then abort;
if col16 > 2100000000 | col16 < -2100000000 then abort;
if col17 > 2100000000 | col17 < -2100000000 then abort;
if col18 > 2100000000 | col18 < -2100000000 then abort;
if col19 > 2100000000 | col19 < -2100000000 then abort;
if col20 > 2100000000 | col20 < -2100000000 then abort;
if col21 > 2100000000 | col21 < -2100000000 then abort;
if col22 > 2100000000 | col22 < -2100000000 then abort;
if col23 > 2100000000 | col23 < -2100000000 then abort;
if col24 > 2100000000 | col24 < -2100000000 then abort;
if col25 > 2100000000 | col25 < -2100000000 then abort;
if col26 > 2100000000 | col26 < -2100000000 then abort;
if col27 > 2100000000 | col27 < -2100000000 then abort;
if col28 > 2100000000 | col28 < -2100000000 then abort;
if col29 > 2100000000 | col29 < -2100000000 then abort;
if col30 > 2100000000 | col30 < -2100000000 then abort;
if col31 > 2100000000 | col31 < -2100000000 then abort;
if col32 > 2100000000 | col32 < -2100000000 then abort;
if col33 > 2100000000 | col33 < -2100000000 then abort;
if col34 > 2100000000 | col34 < -2100000000 then abort;
if col35 > 2100000000 | col35 < -2100000000 then abort;
if col36 > 2100000000 | col36 < -2100000000 then abort;
if col37 > 2100000000 | col37 < -2100000000 then abort;
if col38 > 2100000000 | col38 < -2100000000 then abort;
if col39 > 2100000000 | col39 < -2100000000 then abort;
if col40 > 2100000000 | col40 < -2100000000 then abort;
if col41 > 2100000000 | col41 < -2100000000 then abort;
if col42 > 2100000000 | col42 < -2100000000 then abort;
if col43 > 2100000000 | col43 < -2100000000 then abort;
if col44 > 2100000000 | col44 < -2100000000 then abort;
if col45 > 2100000000 | col45 < -2100000000 then abort;
if col46 > 2100000000 | col46 < -2100000000 then abort;
if col47 > 2100000000 | col47 < -2100000000 then abort;
if col48 > 2100000000 | col48 < -2100000000 then abort;
if col49 > 2100000000 | col49 < -2100000000 then abort;
if col50 > 2100000000 | col50 < -2100000000 then abort;
if col51 > 2100000000 | col51 < -2100000000 then abort;
if col52 > 2100000000 | col52 < -2100000000 then abort;
if col53 > 2100000000 | col53 < -2100000000 then abort;
if col54 > 2100000000 | col54 < -2100000000 then abort;
if col55 > 2100000000 | col55 < -2100000000 then abort;
if col56 > 2100000000 | col56 < -2100000000 then abort;
if col57 > 2100000000 | col57 < -2100000000 then abort;
if col58 > 2100000000 | col58 < -2100000000 then abort;
if col59 > 2100000000 | col59 < -2100000000 then abort;
if col60 > 2100000000 | col60 < -2100000000 then abort;
if col61 > 2100000000 | col61 < -2100000000 then abort;
if col62 > 2100000000 | col62 < -2100000000 then abort;
if col63 > 2100000000 | col63 < -2100000000 then abort;
run;

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

MelissaN — Fri, 02 Aug 2019 00:27:04 GMT

Thanks Tom, I can try that to see if the culprit shows up. Just to clarify that I only need to check the integer columns correct? Thanks.

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

TomKari — Fri, 02 Aug 2019 14:42:43 GMT

I'm glad this helped you. Just to confirm, yes, the code I supplied would only be used on the variables that are defined as 4 byte integer in Teradata. Any variables that are 8 byte integer or any length of floating point would not have a problem (although with floating point, you need to understand at what point you'll start to lose low-end digits with integers).

Tom

Re: The value applied to a Teradata integer was out of range - greater than 2147483647

MelissaN — Fri, 02 Aug 2019 15:01:03 GMT

Hi Tom, yes, that's correct. I used the code you supplied to find the line that are not within that limit (defined as integer 4 byte in Teradata), appeared to happen to only 2 records (caused by entry data errors). Once I fixed the underlying data, I was able to upload everything without any issue. Thanks