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

[ewgrashorn]

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;

[LinusH]

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.

[ewgrashorn]

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

[JBailey]

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

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

[TomKari]

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

[ewgrashorn]

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?

[TomKari]

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".

[ewgrashorn]

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. 

[SASKiwi]

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

[Tom]

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.

[ewgrashorn]

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.

[jakarman]

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.

[JBailey · Accepted solution]

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 */

);

Good luck and best wishes,

Jeff