02-26-2017 10:52 AM - edited 02-26-2017 11:24 AM
I am curious to know why the LENGTH function is not supported in an Explcit Sql Passthrough for SAS/ACESS Interface to Teradata. I am using SAS 9.4M3 on AIX -64 with Teradata Client version 15.10 , Teradata Datbase version 15.10.02.08.
For eg. the below Query ends up in an Error :
Proc sql; connect to teradata(user="test" password="test123" server="T_DEV"); select * from connection to teradata ( select LENGTH('SAMPLE') as LEN ); Disconnect from Teradata; Quit;
The Error in the SAS Log is :
ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.
But using CHAR_LENGTH or CHARACTER_LENGTH function in the above works fine.
Proc sql; connect to teradata(user="test" password="test123" server="T_DEV"); select * from connection to teradata ( select CHAR_LENGTH('SAMPLE') as LEN ); Disconnect from Teradata; Quit;
The Output from the above Code is :
As i understand it , prior to Teradata version 14.0 LENGTH was not a valid Teradata Sql function but CHAR_LENTH / CHARACTER_LENGTH was a valid Function. However starting with Teradata 14.0 LENGTH is an Inbuilt Function ( Embedded Services System Function) and so are Functions LTRIM and RTRIM. I can sucessfully use LTRIM and RTRIM in Explciti Sql Pass through.
So why does the LENGTH function cause an Error? Any Reason?
02-26-2017 11:03 AM - edited 02-26-2017 11:08 AM
The error message is complaining about the vairable type. So why not add a CAST() function to set the type?
select cast(LENGTH('SAMPLE') as integer) as LEN
But really it sounds like a question you should ask your Teradata administrator.
Perhaps your Database is not running version 15. Just because you are using the version 15 driver doesn't mean the database is running version 15. Use this query to find the version.
select * from dbc.dbcinfo;
The LENGTH() function works on my database that is running this version:
InfoKey InfoData 1 RELEASE 15.00.05.10 2 VERSION 15.00.05.10 3 LANGUAGE SUPPORT MODE Standard
02-26-2017 11:31 AM - edited 02-26-2017 11:52 AM
@Tom I did check the teradata version using the query : select * from dbc.dbcinfo and it gives me the below version as i mentioned in my post above :
Obviously your Version is different than mine by Release and Version.
Your suggestion of using the CAST function surrounding the LENGTH function Works but I still do not understand why this has to be used at all.
Also if i run the Query Select LENGTH('SAMPLE') in Teradata Sql Assistant (Version : 126.96.36.199) it works fine. But I do see difference in the Result depending on how i am connecting to Teradata via Sql Assistant . If i use an ODBC Data Source to connect to Teradata via the Sql Assistant the query returns a value of 6.00 but if i use the .NET Provider for Teradata to connect to Teradata via the Sql Assistant i get a Result of 6 ie no decimal places.
02-26-2017 11:52 AM - edited 02-26-2017 11:53 AM
I am not sure how to find the definition of the LENGTH() and CHAR_LENGTH() functions. Googling for Teradata syntax is worse than for SAS syntax. But I did make a table using both and found that result of LENGTH() is defined as type NUMBER and of CHAR_LENGTH() it is defined as type INTEGER. It must be that SAS cannot directly translate the NUMBER data type. Why it doesn't just translate it to FLOAT like it does for most data types I am not sure.
02-26-2017 01:28 PM
As @Tom wrote already the SAS/Access engine seems to not like a data type of NUMBER.
NUMBER is also not listed as one of the supported data types (like the error message tells you). I couldn't find an explanation why NUMBER isn't supported.
Supported numeric data types are:
|FLOAT | REAL | DOUBLE PRECISION|
02-26-2017 01:36 PM - edited 02-26-2017 01:36 PM
@Tom , you mentioned that with your version of Teradata Database , LENGTH function is working. Does that mean it works without the CAST function in the SAS Explicit Sql Pass through and if this is True would mean that the Teradata Engine is able to Translate the NUMBER type to probably INTEGER with your Version of Teradata Database and also Teradata Client/Driver version?
02-26-2017 01:40 PM