SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Reply
Contributor
Posts: 24

ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Hi SAS,

I am trying to execute a PROC SQL query to a teradata database where I only pull data (I perform no computations) but I get this error:

ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

I am aware of this tip:

http://support.sas.com/kb/31/658.html

But as you can see from my code snippet below, I am using teradata mode.  The query works in Teradata SQL assistant.  How can I get it to work in SAS?

Thanks,
Eric

proc sql;

    connect to teradata (user=USERNAME password=PASSWORD

            database=DATABASE server=SERVER mode=teradata connection=global);

    create table alldata as select * from connection to teradata

      (SELECT  distinct var1, var2...

    );

disconnect from teradata;

quit;

Super User
Posts: 5,254

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Not sure why this happens, but try to use:

options debug=',,,d' sastraceloc=saslog nostsuffix;

This will hopefully give you some more information in the log about what Teradata is doing.

Data never sleeps
Contributor
Posts: 24

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Hi Linus, that's a good suggestion.  Unfortunately it didn't provide any further information.

SAS Employee
Posts: 199

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

I am not sure that debug= will work. This is the recommended command.

OPTIONS SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

PROC Star
Posts: 1,085

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Could one of the columns that you are retrieving be in a numeric form that overflows the SAS numeric data type (8 byte floating point). I used to have a similar problem with another DBMS that had an 8 byte integer data type.

Tom

Contributor
Posts: 24

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Yes Tom, that's the only possible answer.  But doesn't it mean that there's something wrong with SAS's implementation of Teradata if the logic executes under Teradata SQL Assistant but throws such a cryptic error under SAS?  And as this link inidicates:31658 - A numeric-overflow error occurs during computation with SAS/ACCESS® Interface to Teradata there are known issues with SAS's implementation.  Is there any way to contact SAS development and ask them to fix this?

PROC Star
Posts: 1,085

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

I'm not sure that I'd call it a cryptic message; it seems to clearly indicate the problem. Also, in terms of Teradata and SAS, you're running this as a pass-through query, so you're taking most of the SAS facilities out of the picture.

The key issue is, if you're trying to retrieve a number that can't be represented correctly in the SAS 8 byte floating point format, you're going to have problems no matter what. I think the usual solution to this is to force a conversion to a character variable, big enough to contain all the digits.

It's a fundamental problem in computer number representation; it can't really be "fixed".

Contributor
Posts: 24

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

But as this is a known issue, apparently, and SAS thinks that they solved it by introducing the mode=teradata.  They didn't.  I'm getting the error that they claim that they solved.  And I stand by my assertion that "during computation" is cryptic because I wasn't doing *ANY* computations, all I was doing was pulling variables out of the database. 

Super User
Posts: 3,100

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

I'd say it is time to track your problem with SAS Tech Support if you haven't done so already.

Super User
Super User
Posts: 6,495

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Are you using COUNT() function? Using MODE=TERADATA the COUNT() function can generate that message if you have too large a number. 

You can fix it by casting the value CAST(count(*) as float)

Or use MODE=ANSI instead.

Contributor
Posts: 24

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

No Other Tom, that's the reason I posted the issue in the first place.  No calculations are taking place, which makes it an especially confusing error code to have thrown.  The issue must be as TomKari describes.

Valued Guide
Posts: 3,208

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Mode=Teradata and mode=ansi do not really help on variable type conversion. This is about commiting %%ItemTitle%%</title>" --><title>Teradata Versus ANSI Session Mode.

Those 8-byte integers can contain values that do not fit into 8 byte floatings, it that is your issue than do not transport your Teradata data to SAS. Let it be there in Teradata why are you copying it to saswork?

You could be confused by your SQL as you are doing a connect statement thinking you are doing explicit SQL coding, wrong. It is still SAS SQL.
See; SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition SQL Pass-Through Facility Specifics for Teradata
The difference for the modes and the usage for explicit SQL is givenm note the "execute  (  )" usage. Remember to specify a table with data or as view as destination within TD not SAS. 

The world is changing is you could look at Proc DS2 SAS(R) 9.4 DS2 Language Reference, Fourth Edition  and FED-SQL SAS(R) 9.4 FedSQL Language Reference, Third Edition.

The difference with the old known SAS types is that they are supporting a lot of other types often used in those rdbms systems.

Incompatible designs cannot be fixed. You have to be ware of those designs and use the best one for your needs.

---->-- ja karman --<-----
SAS Employee
Posts: 199

Re: ERROR: Teradata row not delivered (trget): Numeric overflow occurred during computation.

Hi ewgrashorn,

Which versions of SAS and Teradata are you using? In this case, the word computation probably means conversion.

It is highly likely that you are querying a Teradata view. This view could contain a calculation such as SUM. So, don't rule it out.


SAS is giving you the error - it isn't a problem with the query being sent to Teradata. SAS is telling you that Teradata is returning a value which will not fit into a SAS data type. If you can show your actual query, and the DDL for your table or view, it may help the SAS community solve your problem. If you see NUMBER or BIGINT data types in your table it may be your culprit. 


These commands may help you out. It is best to run them in BTEQ (SQL Assistant should work too).

SHOW displays the DDL used to create the table/view.

SHOW TABLE tablename;

SHOW VIEW viewname;

HELP will show you a list of the columns.

HELP TABLE table_name;

You can run these commands via SAS. The HELP commands format the results better.

proc sql;

    connect to teradata (user=USERNAME password=PASSWORD

            database=DATABASE server=SERVER mode=teradata connection=global);

    create table alldata_help as select * from connection to teradata

      (HELP TABLE mytable); /* the SHOW commands will work here too */

);


I am no longer a SAS employee, so I do not have access to a SAS environment.

Good luck and best wishes,

Jeff

Ask a Question
Discussion stats
  • 12 replies
  • 5465 views
  • 6 likes
  • 7 in conversation