using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant

Analytics
Enthusiast

using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant

Here is the query I am running:

Insert Into db.table2
sel
,order_id
, order_name
, CONCAT('https://website.com/',order_id) order_hyperlink

from db.table1;

When I run the command withing Teradata SQL assistant, it runs without issue, but when I try and run it through BTEQ (either from a shell script or when I just type it into the command line) I get the following error:

*** Failure 3706 Syntax error: expected something between '(' and the strin
g 'https://website.com/' keyword.

Is there a different syntax that needs to be used when executing through BTEQ as opposed to running it through Teradata SQL assistant? Any help on this issue would be greatly appreciated.

2 REPLIES
Enthusiast

Re: using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant

Nevermind, figured it out on my own by using method described here: http://teradatasql.com/how-to-concatenate-in-teradata/

New working query looks like this:

Insert Into db.table2
sel
,order_id
, order_name
, 'https://website.com/'||order_id order_hyperlink

from db.table1;
Senior Apprentice

Re: using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant

CONCAT is an ODBC SQL function which is automatially converted to correct Teradata syntax by the ODBC driver (when a specific option is set).

In the ODBC manual there's a list of those functions.

As you noticed you should avoid them because they're going to fail :-)