Teradata SQL报错：desc与limit关键字间缺少预期内容

错误原因 #

Teradata数据库不支持MySQL/PostgreSQL风格的limit关键字，这是导致语法错误的核心原因，它有专属的语法来限制返回行数。

解决方案 #

方法1：使用TOP关键字（最简方案）

直接在SELECT后添加TOP 1，移除末尾的LIMIT 1即可：

SELECT TOP 1 o.CUSTOMER_NBR 
FROM EDW_SMT_P_US.SALES_ORDER_LINE o
LEFT JOIN cte c
ON o.CUSTOMER_NBR = c.CUSTOMER_NBR  
WHERE c.Cust_Seg = 'E - Elite' 
  AND o.order_dt < '2023-03-06'
ORDER BY o.order_dt DESC

方法2：使用窗口函数（适配复杂行筛选场景）

如果需要更灵活的行控制逻辑，可结合QUALIFY与ROW_NUMBER()窗口函数实现：

SELECT o.CUSTOMER_NBR 
FROM EDW_SMT_P_US.SALES_ORDER_LINE o
LEFT JOIN cte c
ON o.CUSTOMER_NBR = c.CUSTOMER_NBR  
WHERE c.Cust_Seg = 'E - Elite' 
  AND o.order_dt < '2023-03-06'
QUALIFY ROW_NUMBER() OVER(ORDER BY o.order_dt DESC) = 1

额外优化提示 #

原SQL中的LEFT JOIN会被WHERE c.Cust_Seg = 'E - Elite'隐式转换为INNER JOIN（筛选右表非空条件），直接写成INNER JOIN可让逻辑更清晰：

SELECT TOP 1 o.CUSTOMER_NBR 
FROM EDW_SMT_P_US.SALES_ORDER_LINE o
INNER JOIN cte c
ON o.CUSTOMER_NBR = c.CUSTOMER_NBR  
WHERE c.Cust_Seg = 'E - Elite' 
  AND o.order_dt < '2023-03-06'
ORDER BY o.order_dt DESC

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