topic Re: count function does not support by Teradata ODBC engine for SAS/Access in SAS Programming

count function does not support by Teradata ODBC engine for SAS/Access

LL5 — Thu, 08 Oct 2020 13:41:49 GMT

Hi all, I am trying to run a sql pass thru query with ODBC connection to Teradata but it seems the count function is not supported by this engine. I tried a different approach by removing the count function outside of the pass thru query and it ran well.

I want to find out 1) Is there a better/alternative way to accomplish the same goal and 2) why count function is not supported by the odbc engine?

Below are the codes and error message for both approach.

Approach 1 - this gives me error message

PROC SQL;
  CONNECT TO teradata(user=&tduid password=&tdpwd server=axb connection=global mode=teradata fastload=yes fastexport=yes);
  CREATE TABLE inventory AS
    SELECT * 
      FROM CONNECTION TO teradata
        ( SELECT  
          count(inventory) as inv_count, 
          region                                   

        FROM      warehouse.inventory                                                                                                                      
          WHERE sale_date = '2020-10-01'                                                     

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

Approach 2- this works but I wonder a better/alternative way because in reality, I have a lot more fields and tables.

PROC SQL;
  CONNECT TO teradata(user=&tduid password=&tdpwd server=axb connection=global mode=teradata fastload=yes fastexport=yes);
  CREATE TABLE inventory AS
    SELECT count(inventory) as inv_count, 
      region
    FROM CONNECTION TO teradata
      ( SELECT        
        inventory,                                               
        region                                                           
      FROM      warehouse.inventory                                                                                                                      
        WHERE sale_date = '2020-10-01'    
      )  GROUP BY region;

NOTE: Table work.inventory created, with 131 rows and 15 columns.
NOTE: Teradata connection: TPT FastExport has read 38112 row(s).

Re: count function does not support by Teradata ODBC engine for SAS/Access

ChrisNZ — Thu, 08 Oct 2020 22:59:20 GMT

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

This tells me that variable INV_COUNT should probably be created as a datatype that SAS supports.

See here

Re: count function does not support by Teradata ODBC engine for SAS/Access

LL5 — Fri, 09 Oct 2020 12:49:10 GMT

Thanks ChrisNZ. Actually in my production code, I do have cast(variable1 as int) as variable1, cast(variable2 as char(5)) as variable2, etc. The inventory variable does have datatype that SAS supports. The issue appears to have something to do with the count function because no matter what variable I put inside the count function, i am getting the same error message.

Re: count function does not support by Teradata ODBC engine for SAS/Access

Kurt_Bremser — Fri, 09 Oct 2020 12:54:13 GMT

Do a CAST to INT for the result of the COUNT function.

Re: count function does not support by Teradata ODBC engine for SAS/Access

LL5 — Fri, 09 Oct 2020 13:19:41 GMT

This works. Since Inventory is a character field, I did not even though about casting it to INT for the count function.

Thanks a lot for this great tip.

CAST(COUNT(INVENTORY) AS INT) AS INV_COUNT