topic Re: Syntax error after migration to teradata in SAS Programming

Syntax error after migration to teradata

Priyanka_b — Fri, 02 Jun 2017 17:25:44 GMT

Please advise if I have to use different syntax for teradata? Same code works for db2.

ERROR: Teradata prepare: Syntax error: expected something between '(' and ')'

PROC SQL;

CONNECT TO teradata as APRTERA (user="xxxx" PASSWORD="xxxxx" tdpid=APRTERA mode=teradata);

CREATE TABLE MYLIB.SSICORE (COMPRESS=YES) AS SELECT * FROM CONNECTION TO APRTERA (

SELECT RAND() as IDX,

M.PROD_ACCT_NO,

M.ACCESS_MEDIUM_NO,

M.ACCT_CYCL_NO,

M.GRP_CD,

M.GRP_TRANSPARENT_IN,

M.PAPER_SUPRS_IN,

L.CUST_LANGUAGE_CD,

G.GROUP_NM

FROM DWTFORMCRDT0.ACCT_MED_CHG&MONTH_END AS M INNER JOIN

DWTFORMCRDT0.ACCT_LOW_CHG&MONTH_END AS L

ON M.PROD_ACCT_NO=L.PROD_ACCT_NO

AND M.APL_SFX_NO = 0

AND L.APL_SFX_NO = 0

AND M.GRP_TRANSPARENT_IN = 'N'

LEFT JOIN DWTFORMCRDT0.GROUP AS G

ON M.GRP_CD=G.GROUP_CD

ORDER BY M.PROD_ACCT_NO,IDX FETCH FIRST 100000 ROWS ONLY

);

DISCONNECT FROM APRTERA;

QUIT;

RUN;

Log

     PROC SQL;
25             connect to &aprtera;
SYMBOLGEN: Macro variable APRTERA resolves to teradata as aprtera (user=xxxxx
            password="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" tdpid=aprtera mode=teradata )
26              CREATE TABLE MYLIB.SSICORE (COMPRESS=YES) AS SELECT * FROM CONNECTION TO APRTERA (
27                    SELECT RAND( ) as IDX,
28                            M.PROD_ACCT_NO,
29                        M.ACCESS_MEDIUM_NO,
30                            M.ACCT_CYCL_NO,
31                            M.GRP_CD,
32                            M.GRP_TRANSPARENT_IN,
33                            M.PAPER_SUPRS_IN,
34                            L.CUST_LANGUAGE_CD,
35                            G.GROUP_NM
SYMBOLGEN: Macro variable MONTH_END resolves to _1604
36                    FROM DWTFORMCRDT0.ACCT_MED_CHG&MONTH_END M
SYMBOLGEN: Macro variable MONTH_END resolves to _1604
37                          ,DWTFORMCRDT0.ACCT_LOW_CHG&MONTH_END L
38                    ON M.PROD_ACCT_NO=L.PROD_ACCT_NO
39                     AND M.APL_SFX_NO = 0
40                     AND L.APL_SFX_NO = 0
41                     AND M.GRP_TRANSPARENT_IN = 'N'
42                    LEFT JOIN DWTFORMCRDT0.GROUP AS G
43                   ON M.GRP_CD=G.GROUP_CD
44                    ORDER BY M.PROD_ACCT_NO,IDX FETCH FIRST 100000 ROWS ONLY
45              );
ERROR: Teradata prepare: Syntax error: expected something between '(' and ')'. SQL statement was: SELECT RAND( ) as IDX,
       M.PROD_ACCT_NO, M.ACCESS_MEDIUM_NO, M.ACCT_CYCL_NO, M.GRP_CD, M.GRP_TRANSPARENT_IN, M.PAPER_SUPRS_IN, L.CUST_LANGUAGE_CD,
       G.GROUP_NM FROM DWTFORMCRDT0.ACCT_MED_CHG_1604 M ,DWTFORMCRDT0.ACCT_LOW_CHG_1604 L ON M.PROD_ACCT_NO=L.PROD_ACCT_NO AND
       M.APL_SFX_NO = 0 AND L.APL_SFX_NO = 0 AND M.GRP_TRANSPARENT_IN = 'N' LEFT JOIN DWTFORMCRDT0.GROUP AS G ON
2 The SAS System                                                                                        16:46 Thursday, June 1, 2017

M.GRP_CD=G.GROUP_CD ORDER BY M.PROD_ACCT_NO,IDX FETCH FIRST 100000 ROWS ONLY.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
46 DISCONNECT FROM APRTERA;
NOTE: Statement not executed due to NOEXEC option.

Re: Syntax error after migration to teradata

ballardw — Fri, 02 Jun 2017 17:36:05 GMT

If the RAND you are using is intended to be the SAS RAND function it requires the use of a distribution to indicate what type of rand number you want such as Rand('uniform').

Re: Syntax error after migration to teradata

kiranv_ — Fri, 02 Jun 2017 21:16:00 GMT

Code migration from one rdbms to another rdbms is sometimes not very straightforward. I was once involved involved in migration of oracle to Teradata migration.

Teradata does not have rand() function and also it does not have fetch first 10000 rows only. teradata has random function but I am not sure whether it is equivalent to DB2 rand() function. Fetch 10000 rows can be replaced by using select top 10000 variables from teradata table.

Just want to say, check all functions and other things, while doing code migration. Even though it is all SQL, every rdbms have thier own different functions and how they operate are sometimes very different. Teradata which is MPP system is quite different from others.

Bottom line is your code migration will not be straightforward and you may have to make many changes.

Re: Syntax error after migration to teradata

Tom — Sun, 04 Jun 2017 06:40:19 GMT

You probably want the RANDOM() function. It returns and integer between the bounds given. You could divide by something to make it look more like a floating point if you want.

You probably want the SELECT TOP N .... syntax to limit the results.

You also might want to get in the habit of quoting your object names as unlike SAS in Teradata it gets mad if you use a keyword for a table or variable name. So perhaps you need to added quotes around the table name GROUP?

 CREATE TABLE MYLIB.SSICORE (COMPRESS=YES) AS
 SELECT * FROM CONNECTION TO APRTERA
 (SELECT /*NEW*/ TOP 100000
        /*CHANGED*/ RANDom(1,100000)/100000.0 as IDX
       , M.PROD_ACCT_NO
       , M.ACCESS_MEDIUM_NO
       , M.ACCT_CYCL_NO
       , M.GRP_CD
       , M.GRP_TRANSPARENT_IN
       , M.PAPER_SUPRS_IN
       , L.CUST_LANGUAGE_CD
       , G.GROUP_NM
  FROM DWTFORMCRDT0.ACCT_MED_CHG&MONTH_END AS M
  INNER JOIN DWTFORMCRDT0.ACCT_LOW_CHG&MONTH_END AS L
     ON M.PROD_ACCT_NO=L.PROD_ACCT_NO
    AND M.APL_SFX_NO = 0
    AND L.APL_SFX_NO = 0
    AND M.GRP_TRANSPARENT_IN = 'N'
  LEFT JOIN DWTFORMCRDT0."GROUP" AS G
     ON M.GRP_CD=G.GROUP_CD
  ORDER BY M.PROD_ACCT_NO,IDX
  /* REMOVED */ /* FETCH FIRST 100000 ROWS ONLY */
  );

Re: Syntax error after migration to teradata

LinusH — Sun, 04 Jun 2017 11:20:18 GMT

Just to clarify, you are asking the SAS community of Teradata specific syntax, when migrating from another non SAS RDBMS? It feels likw you can get more accurate answers elsewhere (not saying that answers give so far is inaccurate!).
If you were using implicit pass through it would be a different story.

Re: Syntax error after migration to teradata

Priyanka_b — Mon, 05 Jun 2017 21:15:41 GMT

Thanks every one for inputs! Looks like issue appeared to be with view name being different in Teradata. Modifying it resolved the syntax error.