DATA Step, Macro, Functions and more

an ERROR in log while taking count from Teradata DBMS

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

an ERROR in log while taking count from Teradata DBMS

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


Accepted Solutions
Solution
‎05-06-2016 08:55 PM
SAS Super FREQ
Posts: 682

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

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


All Replies
Solution
‎05-06-2016 08:55 PM
SAS Super FREQ
Posts: 682

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

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

 

 

Contributor
Posts: 40

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

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.

Contributor
Posts: 40

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

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.

Super User
Super User
Posts: 6,498

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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