All Forums Database
muzz 2 posts Joined 05/09
29 May 2009
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 examplewith recursive TABLE1(id , firstname , lastname)as(select columnname from a inner join bon left(id,3) from TABLE1--where substr(id,length(id) , 1)='0'--here the id column is a varcharwhen 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 .

Fred 1096 posts Joined 08/04
30 May 2009

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)?

You must sign in to leave a comment.