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).
Do a CAST to INT for the result of the COUNT function.
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
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.
Do a CAST to INT for the result of the COUNT function.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.