Desktop productivity for business analysts and programmers

Error while pulling the data from Teradata

Reply
Contributor
Posts: 36

Error while pulling the data from Teradata

Hi All,

Here am facing an error while extracting the data from Teradata to SAS by using SQL pass through facility in SAS?EG.

ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.

what could be the reason?
Please advice?
Contributor
Posts: 36

Re: Error while pulling the data from Teradata

Hi,

I got resolved this issue.

Thanks,
Kavi
Contributor
Posts: 22

Re: Error while pulling the data from Teradata

SAS/Access to Teradata does not support BIGINT data, and it is usually the offending data type.  However, other data types are supported but still may cause data truncation and errors.  For example, DECIMAL(38,15) is supported by SAS/Access, but it has an invalid SAS format, so it creates a runtime format error in SAS.

An easy way to find BIGINT or other unsupported data is to do a Proc Contents on the table using the libname access method.  The erroneous columns will be in the SAS log.  You can also look in the listing for DECIMAL values where they are longer than 15 digits -- you may want to query the max and min for those columns before you cast them to something that SAS can read.

SAS only represent 15 digits with exact precision in 64-bit operating systems.  You need to cast those bigger decimal types as DECIMAL(15) if they are in bounds, or CHARACTER(20) (or larger) if they are not.  Note that character keys are discouraged because they are slow to access in joins and they take up more space.

New User mma
New User
Posts: 1

Re: Error while pulling the data from Teradata

Hello Kavi,

I'm facing the same issue and i wanted to ask you how did you solve it? I'm doing a query over a table that has one column not supported and even if i'm not selecting it, it's giving me this error.

Thanks in advance!

 

Regards,

Mónica

Esteemed Advisor
Posts: 5,198

Re: Error while pulling the data from Teradata

I think that this is explained in many threads.
You need to either
- use explicit SQL pass through
- have a view created in TD that either omits or casts columns w unsupported data types
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 925 views
  • 0 likes
  • 4 in conversation