BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nbonda
Obsidian | Level 7

Hi

I am getting below error while using SQL pass-through, but does not throw error  if I use Implicit LIBNAME method.

 

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

code:

proc sql;

connect to teradata as tera_

(user=TEST_USER pass=Test_pa$$ mode=teradata schema=TESTDB server=dbserver );

create table d_count as select * from connection to tera_

(select count(*) as count_rows from TESTDB.SAMPLE_TABLE

 

) ;

quit;

 

there are no special columns in Teradata table,below is the definition of the same table:

CREATE MULTISET TABLE dbase.TEST_NEWFILETINVLSTAT_TEST ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ID_1 INTEGER NOT NULL,
      DOC_INVALID_STATUS VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
      FILE_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      PROCESS_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      RUNDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      UNI_ID DECIMAL(13,0) NOT NULL)
PRIMARY INDEX ( ID_1 ,DOC_INVALID_STATUS );

 

Any help is appreciated:

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Hi

 

I guess it has something to do with the data type BIGINT, the count(*) as count_rows probably returns a BIGINT.

 

So Check out this SAS Note http://support.sas.com/kb/39/831.html

 

Bruno

 

 

View solution in original post

4 REPLIES 4
BrunoMueller
SAS Super FREQ

Hi

 

I guess it has something to do with the data type BIGINT, the count(*) as count_rows probably returns a BIGINT.

 

So Check out this SAS Note http://support.sas.com/kb/39/831.html

 

Bruno

 

 

nbonda
Obsidian | Level 7

thank you for your reply.

When I cast to decimal its working fine. But I have issue with the query only for some environment, same query (without casting) working in other envrironments. we have oberved that recently teradata client has been upgraded to Version 15. this client upgrade causing count(*) to convert it into BIGINT datatype.

nbonda
Obsidian | Level 7

Here is the solution for my question. In Teradata 15.1 the default datatype for count(*) is BIGINT , where as Teradata 15.0 this datatype is INT. so we got this problem when I we connect the Teradata 15.1.

Tom
Super User Tom
Super User

So Teradata fixed one problem (count(*) overflowing) and introduced a new one (non-standard data type).

 

Things haven't chnaged much in the computer industry in the last 35 years.  My first job out of college I was implementing PCOs (program change orders).  Most of them were to fix issues caused by earlier PCOs.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4180 views
  • 0 likes
  • 3 in conversation