Search code examples
sqlteradatastring-concatenation

Can't use CONCAT in Teradata from SQLWorkbenchJ

I have a script that used to run just fine from Teradata SQL Assistant. I am unable to get the exact same script to run from SQL Workbench/J. I have isolated the problem to one specific line. Here's the query:

SELECT
    variable1 as name1,
    variable2 as name2,
    CONCAT(TRIM(variable3), ':', trim(variable4)) as name3
    variable4 as name4,
FROM
    table1
WHERE
    variable4 between '2017-01-01' AND '2017-01-31';

The problem is the CONCAT line. If I comment that line out, code runs fine. If I leave that line in, I get the unhelpful message:

[Teradata Database] [TeraJDBC 16.10.00.07] [Error 3706] [SQLState 42000] 
Syntax error: expected something between '(' and the 'TRIM' keyword. [SQL State=42000, 
DB Errorcode=3706]
1 statement failed.

I say unhelpful, because that makes it sound like a syntax error, but this isn't a syntax error as far as Teradata is concerned. The exact same code ran fine on Windows Teradata SQL Assistant. But since OSX Teradata SQL Assistant is a dumpster fire, I have to try and run the script in SQL Workbench J.

Help, please?

Solution

  • There's no concat function in Teradata SQL, it's an ODBC function (sometimes) automatically translated by the ODBC driver to valid syntax. But SQL Workbench uses JDBC, which doesn't support this function.

    Simply switch to Standard SQL ||:

    TRIM(variable3) || ':' || trim(variable4)