Teradata宏语法报错：期望在';'与请求末尾间添加')'

错误根源分析 #

你的宏代码存在两处语法问题：

• WHERE子句中(Poly.geometry.ST_Contains(Fast.geometry) = 1;)写法错误：多余了末尾的分号，同时外层括号属于冗余，导致数据库解析时无法正确识别语句结构。
• 宏定义的外层括号与内部语句的分号搭配不当，触发语法解析冲突。

修正后的宏代码 #

REPLACE MACRO GEO_MATCHED (REGION INTEGER) AS (
    INSERT INTO DLA_v.GEO_FULL_2019_MATCHED
    SELECT                
        Poly.Project_ID AS "Project_ID",
        Poly.WIKIMAPIA_ID AS "POLY_ID_WIKIMAPIA",
        Poly.NAME AS "POLY_NAME",
        Fast.REVENUE_YTD,
        Fast.JOB_VERTICAL,
        Poly.geometry.ST_Contains(Fast.geometry) AS "INSIDE"
    FROM
        (SELECT * FROM DLA_A_19143.geo_jobsite_input_data WHERE region__c = :REGION) Fast,
        DLA_D_SLSMKT.GEO_POLYGONS Poly
    WHERE
        Poly.geometry.ST_Contains(Fast.geometry) = 1
);

关键修改点 #

1. 移除WHERE条件中的冗余括号和末尾的分号，将(Poly.geometry.ST_Contains(Fast.geometry) = 1;)改为Poly.geometry.ST_Contains(Fast.geometry) = 1
2. 确保宏定义的外层括号正确闭合，内部INSERT语句结尾不需要额外分号（宏的AS()结构会处理语句分隔）

额外优化建议 #

建议将隐式逗号连接改为显式JOIN语法，提升代码可读性和维护性，示例如下：

REPLACE MACRO GEO_MATCHED (REGION INTEGER) AS (
    INSERT INTO DLA_v.GEO_FULL_2019_MATCHED
    SELECT                
        Poly.Project_ID AS "Project_ID",
        Poly.WIKIMAPIA_ID AS "POLY_ID_WIKIMAPIA",
        Poly.NAME AS "POLY_NAME",
        Fast.REVENUE_YTD,
        Fast.JOB_VERTICAL,
        Poly.geometry.ST_Contains(Fast.geometry) AS "INSIDE"
    FROM
        (SELECT * FROM DLA_A_19143.geo_jobsite_input_data WHERE region__c = :REGION) Fast
    JOIN DLA_D_SLSMKT.GEO_POLYGONS Poly
        ON Poly.geometry.ST_Contains(Fast.geometry) = 1
);

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