SAS DI执行CAST语句报错求助：Teradata SQL语法错误

首先咱们直接定位到报错的核心原因：你提供的报错代码行末尾多了一个英文句号（.）：

SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID.

Teradata的SQL语法里，列别名后面绝对不能加这个句号，这直接导致了语法解析失败，也就是你看到的那一大串预期符号的错误提示。

修正步骤 #

1. 移除别名后的多余句号：把所有类似AS EV_ID.的写法改成AS EV_ID，确保没有多余的标点符号。
2. 检查完整SQL中的同类问题：遍历嵌套在connection to teradata()里的所有SQL语句，确认所有列别名、表别名后面都没有意外的标点。

修正后的完整代码 #

proc sql; 
    connect to teradata (user=&tera_usr pass="&tera_pw" server=tdprod); 
    CREATE TABLE OVERALL_SAMPLE_TF AS 
    SELECT * FROM connection to teradata( 
        SELECT 
            A.EV_ID, A.CreatedDate, A.CompletionDate, A.SortCode, A.SenderBIC, A.ReceiverBIC,
            A.NumberOfHitsPerAlert, A.Level1ExecutionUser, A.Level2ExecutionUser, A.Final_Level,
            A.Currency, A.Iban, A.Account_Number, A.T_Transaction, A.Last_Operator, A.MessageID,
            A.Message_Type, A.Nature, A.Decision_Type, A.Branch_Contact, A.Other_Bank_Contact,
            A.CompletionDate_TIMESTAMP, A.Amount, B.Franchise, B.Sub_Franchise, B.Sub_Sub_Franchise
        FROM (
            SELECT X.*,Y.DIV_ID 
            FROM ( 
                SELECT 
                    CAST(EV_ID as VARCHAR(20)) AS EV_ID, 
                    CreatedDate, CompletionDate, SortCode, SenderBIC, ReceiverBIC,
                    NumberOfHitsPerAlert, Nonblocking, Level1ExecutionUser, Level2ExecutionUser,
                    Final_Level, Currency, Iban, Account_Number, T_Transaction, Last_Operator,
                    MessageID, Message_Type, Nature, Decision_Type, Branch_Contact, Other_Bank_Contact,
                    CompletionDate_TIMESTAMP, Amount 
                FROM BAC_AML.EVENT_STATUS_ALERT_TF 
                WHERE 
                    CompletionDate >= ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1) 
                    AND CompletionDate <= (current_date - extract(day from current_date)+1)-1 
                    AND CompletionDate NE '99991231' (date, format 'yyyymmdd') 
                    AND Final_Level>0 
                    AND UPPER(DECISION_TYPE) IN ('PASS', 'PASS_L1', 'FAIL', 'UNQUEUE', 'RECHECKED')
                QUALIFY ROW_NUMBER() OVER (PARTITION BY EV_ID ORDER BY CompletionDate_TIMESTAMP DESC) = 1 
            ) AS X 
            LEFT JOIN (
                SELECT 
                    CAST(EV_ID as VARCHAR(20)) AS EV_ID, 
                    CAST(DIV_ID as VARCHAR(20)) AS DIV_ID 
                FROM BAC_AML.TF_FRANCHISE 
            ) AS Y ON X.EV_ID=Y.EV_ID 
        ) AS A 
        LEFT JOIN (
            SELECT 
                CAST(FR_KEY as VARCHAR(20)) AS FR_KEY, 
                L1_Empos_Name, L2_Emp_Posn_Name, L3_Emp_Posn_Name, L4_Emp_Posn_Name,
                Franchise, Sub_Franchise, Sub_Sub_Franchise, ORG_MODEL_LINK 
            FROM BAC_AML.FRANCHISE_LOOKUP 
            WHERE UPPER(FRANCHISE) IN ('PBB','CPB','ULSTER','NWM') 
        ) AS B ON B.FR_KEY=A.DIV_ID
    ); 
    disconnect from teradata; 
quit;

补充说明 #

至于为什么这段代码在SAS EG里能正常运行，大概率是EG的SQL解析器对这种微小的标点错误有一定容错性，但SAS DI在提交SQL到Teradata时是直接传递原生语句，Teradata的解析器对语法要求更严格，所以暴露了这个问题。

内容的提问来源于stack exchange，提问作者user3096300