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.
with recursive TABLE1(id , firstname , lastname)
select columnname from a
inner join b
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 .
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)?