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

[pchegoor]

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.

[Tom]

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

[pchegoor]

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

[Tom]

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.

[Patrick]

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#n0v7nh4ylrihtin1te...

[pchegoor]

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

[Patrick]

@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

[pchegoor]

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