topic Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad in SAS Data Management

LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Teradata

pchegoor — Sun, 26 Feb 2017 16:24:53 GMT

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?

Thanks.

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

Tom — Sun, 26 Feb 2017 16:08:53 GMT

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

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

pchegoor — Sun, 26 Feb 2017 16:52:24 GMT

@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 : 15.10.1.3) 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.

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

Tom — Sun, 26 Feb 2017 16:53:26 GMT

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.

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

Patrick — Sun, 26 Feb 2017 18:28:44 GMT

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:

Numeric data:

BYTEINT	INTEGER
DECIMAL(n,m)	SMALLINT
FLOAT \| REAL \| DOUBLE PRECISION

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0v7nh4ylrihtin1te8xl0q3dvzv.htm

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

pchegoor — Sun, 26 Feb 2017 18:36:55 GMT

@Tom and @Patrick Thanks you for your valuable Insights into this Issue and I guess you both are right as i see no other reason for this Error.

@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?

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

Patrick — Sun, 26 Feb 2017 18:40:10 GMT

@pchegoor and @Tom

Now Googling with the right keywords and I found the official SAS explanation why NUMBER isn't supported.

http://support.sas.com/kb/59/004.html

Re: LENGTH Function not supported in Explicit Sql Passthrough for SAS/ACCESS Interface to Terad

pchegoor — Sun, 26 Feb 2017 18:43:16 GMT

@Patrick Thanks for the SAS Support link above .Does make sense now.