All Forums Database
rsaliba1122 2 posts Joined 11/15
20 Jan 2016
SQL Question about use of LEFT, RIGHT and SUBSTR functions

WHY DOES QRY1 WORK AND QRY2 WORKS BUT QRY3 DOES NOT?
 
QRY1:
SELECT   SUBSTR(P.PHONE,1,3) ,
              SUBSTR(P.PHONE,4,3) ,
              SUBSTR(P.PHONE,7,4)
FROM    PhoneNumbers  p ;
 
QRY2:
SELECT  LEFT(P.PHONE,3),
               RIGHT(P.PHONE,4)
FROM     PhoneNumbers  p ;
 
QRY3: - error message= 3706 - ”Expected something between the Select keyword and the Left keyword”
SELECT   LEFT(P.PHONE,3) ,
               SUBSTR(P.PHONE,4,3) ,
               RIGHT(P.PHONE,4)
FROM     PhoneNumbers  p ;
 
Thanks for any insight you can give - things like this puzzle me! 

Richard
dnoeth 4628 posts Joined 11/04
20 Jan 2016

Hi Richard,
if you run TD15.10 all three should work, before both LEFT and RIGHT were not built-in, but the ODBC (JDBC?)  driver might rewrite them to vaild TD syntax.
So what's your TD release and how do you connect, ODBC/JDBC/.NET?

Dieter

rsaliba1122 2 posts Joined 11/15
20 Jan 2016

TD14.1 and I am currently using an ODBC connection

Richard

tomnolan 594 posts Joined 01/08
20 Jan 2016

>>> (JDBC?)  driver might rewrite them to vaild TD syntax
 
Since TTU 8.1 (over 10 years ago), the Teradata JDBC Driver only replaces JDBC escape syntax inside curly braces { } with the proper Teradata SQL syntax. The Teradata JDBC Driver does not change any SQL request text outside JDBC escape syntax curly braces.
 
Here is a link to the Teradata JDBC Driver User Guide section about JDBC escape syntax:
http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#BGBDDAFD
 

You must sign in to leave a comment.