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

[MelissaN]

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

[TomKari]

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

[MelissaN]

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.

[TomKari · Accepted solution]

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;

[MelissaN]

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.

[TomKari]

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

[MelissaN]

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