topic Re: Teradata Passthrough Syntax Error Troubleshoot in SAS Enterprise Guide

Teradata Passthrough Syntax Error Troubleshoot

IgawaKei29 — Tue, 07 May 2024 21:06:32 GMT

I have been looking at this code syntax for hours, the Teradata code runs in a different environment, but bringing it through Enterprise Guide is giving me issues. The error I am getting is: ERROR: Teradata execute: Syntax error: expected something between ')' and ';'. I can't seem to figure out what the issue is. I appreciate any help or insight on this.

PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=xx  server="xxx" connection=global mode=teradata);
  execute (drop table TestServer.Test3) BY Teradata;
  execute (create table TestServer.Test3 as
  
(
select *
from TestServer.Test1

UNION ALL

select * 
from TestServer.Test2

))BY Teradata;
 quit;

Re: Teradata Passthrough Syntax Error Troubleshoot

SASKiwi — Tue, 07 May 2024 21:40:21 GMT

Try it without the extra brackets:

PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=xx  server="xxx" connection=global mode=teradata);
  execute (drop table TestServer.Test3) BY Teradata;
  execute (create table TestServer.Test3 as
select *
from TestServer.Test1
UNION ALL
select * 
from TestServer.Test2
) BY Teradata;
 quit;

Re: Teradata Passthrough Syntax Error Troubleshoot

Tom — Tue, 07 May 2024 22:26:46 GMT

In Teradata syntax there are two key things you have left out.

1) At the end of the CREATE TABLE statement you need some like WITH DATA (look up the syntax I don't remember it exactly) if you actually want the data to be saved.

2) Teradata is a highly parallel setup. It does this by partitioning the data with a PRIMARY INDEX. You want a primary index that does not skew the data so that only some of the "nodes" have all of the data. If you do not specify the PRIMARY INDEX then it will pick the first variable. Which might be a very poor choice.

Re: Teradata Passthrough Syntax Error Troubleshoot

IgawaKei29 — Tue, 07 May 2024 23:31:46 GMT

I tried without the extra brackets and got the message:
ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or '(' between the 'as'
keyword and the 'select' keyword.

Re: Teradata Passthrough Syntax Error Troubleshoot

IgawaKei29 — Tue, 07 May 2024 23:32:48 GMT

Thank you! That was it.. I was missing the With Data and Primary Index! Really appreciate the help.

) with data primary index(id)) by teradata;