BTEQ syntax with CAST embedded in SUBSTRING function

Database

BTEQ syntax with CAST embedded in SUBSTRING function

Hi,

I am trying to achieve something that seem trivial enough but so far all my attempts failed. I have a BTEQ session called from a Shell script, what I try to do is to create a string with a "some blanks" the size of this blank section is determined by a shell variable) 

So what i have is : 

SELECT

<...> ||  SUBSTRING(CAST(' ' as CHAR(${MAX_SPC_NB})  ) , 1, ${SPC_NB}  ) | || <...>

FROM

Where MAX_SPC_NB > SPC_NB and both are determined on the fly (Can't harcode any of them) 

The error i get : 

             ' || SUBSTRING(CAST(' ' AS  CHAR(8192)) , 1, 256  ) || '

                                                                               $

 *** Failure 3706 Syntax error: expected something between ')' and ','.

The same query called from SQLAssistant is working fine btw. 

4 REPLIES
Senior Apprentice

Re: BTEQ syntax with CAST embedded in SUBSTRING function

I doubt it's working in SQLA, because you mix SUBSTR and SUBSTRING syntax, it's either

 SUBSTRING(CAST(' ' AS  CHAR(8192)) FROM 1 FOR 256  ) 

or

 SUBSTR(CAST(' ' AS  CHAR(8192)) , 1, 256  ) 

I'm not going to ask why don't you use a simple CAST(' ' as CHAR(${SPC_NB}) instead :-)

Dieter

Re: BTEQ syntax with CAST embedded in SUBSTRING function

Ok, I did not even notice that SUBSTR & SUBSTRING were behaving differently.

I am new to the Teradata DB and I inherited this piece of code, I clearly did not challenged enough the existing code. Anyway, the solution CHAR(${SPC_NB})   is also raising the error.

I tried the others and the one that works is 

SUBSTRING(CAST(' ' AS  CHAR(${MAX_SPC_NB})) FROM 1 FOR  SPC_NB ) 

Thanks for pointing me to the right direction, really appreciated. 

Re: BTEQ syntax with CAST embedded in SUBSTRING function

Ok, I did not even notice that SUBSTR & SUBSTRING were behaving differently.

I am new to the Teradata DB and I inherited this piece of code, I clearly did not challenged enough the existing code. Anyway, the solution CHAR(${SPC_NB})   is also raising the error.

I tried the others and the one that works is 

SUBSTRING(CAST(' ' AS  CHAR(${MAX_SPC_NB})) FROM 1 FOR  ${SPC_NB} ) 

Thanks for pointing me to the right direction, really appreciated. 

N/A

Re: BTEQ syntax with CAST embedded in SUBSTRING function

Bless you Dieter!