Recursive Query and substring error

Database
Fan

Recursive Query and substring error

Hi all,

I am new to teradata. I am writing a query which retreives a list of joins of table in a recursive table .

Then from this recursive table I want to filter the data based on the substring of the ID.

For example

with recursive TABLE1(id , firstname , lastname)

as

(

select columnname from a

inner join b

on a.id=b.id

)

select left(id,3) from TABLE1

--where substr(id,length(id) , 1)='0'

--here the id column is a varchar

when I run this I get an error saying " Something missing between SELECT and LEFT ("

But when I just say select first_name from table1 It works perfect.

Please help me in solving my problem.
Also My main Idea is to hold the data from the joins into a table (like a common table expression in SQL Server), table variable will be ok but not a volatile table .
1 REPLY
Teradata Employee

Re: Recursive Query and substring error

LEFT() is not a Teradata SQL function. SUBSTRING(ID FROM 1 FOR 3) or SUBSTR(ID,1,3) should work.
By the way, LENGTH() - in your comments - is not a Teradata function either; use CHARACTER_LENGTH or CHARACTERS.

There are no "table variables" in Teradata SQL and "WITH" common table expression support is only available in the latest releases and still rather limited. Why do you insist on not using a VOLATILE table (which would seem to satisfy your intent)?