topic SQL Syntax error expected something between '(' and the 'left' keyword in Talend Studio

SQL Syntax error expected something between '(' and the 'left' keyword

talendtester — Sat, 16 Nov 2024 12:33:16 GMT

My data looks like:
yyyymmdd
20111122
The Teradata database SQL is converting the varchar to a date so it looks like:
2011-11-22
My SQL:
SELECT
cast(left( myDate, 4) || '-' ||
substr(myDate, 5,2) || '-' ||
substr(myDate,length(myDate) - 2 + 1, 2) as date ) as PULL_DATE
FROM myDatabase.myTable
How can I get this SQL to run successfully in a tTeradataInput component?
The SQL runs fine outside of Talend.
The error message:
Exception in component tTeradataInput_3
com.teradata.jdbc.jdbc_4.util.JDBCException: Syntax error: expected something between '(' and the 'left' keyword.
Do I need to somehow escape the word "left" or "cast"?

Re: SQL Syntax error expected something between '(' and the 'left' keyword

janhess — Wed, 23 Nov 2011 09:06:22 GMT

Have you got the SQL in quotes?

Re: SQL Syntax error expected something between '(' and the 'left' keyword

talendtester — Wed, 23 Nov 2011 17:14:18 GMT

I have double quotes before and after the SQL. In my SQL I have single quotes.
Do I need to escape the vertical bars individually with "\\"?