topic an ERROR in log while taking count from Teradata DBMS in SAS Programming

an ERROR in log while taking count from Teradata DBMS

nbonda — Tue, 03 May 2016 11:45:18 GMT

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

Re: an ERROR in log while taking count from Teradata DBMS

BrunoMueller — Tue, 03 May 2016 12:26:33 GMT

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

Re: an ERROR in log while taking count from Teradata DBMS

nbonda — Wed, 04 May 2016 16:42:22 GMT

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.

Re: an ERROR in log while taking count from Teradata DBMS

nbonda — Sat, 07 May 2016 00:58:18 GMT

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.

Re: an ERROR in log while taking count from Teradata DBMS

Tom — Sat, 07 May 2016 02:34:09 GMT

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.