All Forums Database
29 Dec 2014
INSTR and SUBSTRING are not working in SHELL SCRIPT

Hi 
 
Iam Using INSTR and SUBSTRING in UNIX shell script. They are working in Teradata sql assistant but they both are not working in UNIX SHELL SCRIPT.
I changed SUBSTRING to SUBSTR and it worked. But i still have problem with INSTR. Can any one help me out.
 
Example :
 

select

case when  SubRegion like '%REGION%' then SubRegion  else SubRegion || ' '

 || 'REGION' end REGION_NAME,

SUBSTR(nodes  FROM instr(nodes,'-',1,1)+1 for instr(nodes,'-',1,1)-1) AS node, 

SgSpeed, 

SgUtil,

PortCount, 

CAST(WeekEndingDate as DATE) WEEKENDINGDATE

FROM RNL_VIEWS.WT_CmtsSgUtil

WHERE instr(nodes,'-') > 0

and WeekEndingDate =  '2014-12-06'

 

ERROR:

 

SUBSTR(nodes  FROM instr(nodes,'-',1,1)+1 for instr(nodes,'-',1,1)-1) AS n

ode, 

                   $

 *** Failure 3706 Syntax error: expected something between the word 'nodes' 

 and the 'FROM' keyword.

                Statement# 1, Info =582 

 *** Total elapsed time was 1 second.

 

 
 
Thanks
Naveen

dnoeth 4628 posts Joined 11/04
30 Dec 2014

Hi Naveen,

There are two variations of substring in Teradata:

SUBSTRING(str FROM startpos FOR length) -- Standard SQL
SUBSTR(str, startpos, length) -- Teradata SQL

SUBSTR(str FROM startpos FOR length) doesn't exist, but the ODBC driver might rewrite ODBC-style SUBSTRING(str, startpos, length). This is also done for other ODBC-functions like LEFT or MONTH. Whenever you try to submit such a query using CLI/.NET/JDBC it will fail.

 

To disable this rewrite you to need to check the 'Disable Parsing' in the ODBC driver's options.

 

Regarding INSTR, this is included since TD14 and should not cause any error.

Dieter

30 Dec 2014

For SUBSTRING i used SUBSTR and unix shell script recognized. But i Have Problem with INSTR in Shell script

dnoeth 4628 posts Joined 11/04
30 Dec 2014

The variation you wrote will never work in shell (=BTEQ), remove the INSTR and you still get the same error while INSTR standalone will work.

Dieter

You must sign in to leave a comment.