Snowflake中SQL子查询别名metrics触发编译语法错误问题求助

问题复现 #

你执行的原始SQL语句：

(SELECT *,'{"Source": "010","Keys": [{"Value": "CHARGE"}]}' AS ID1 
FROM DETAILS) metrics
JOIN DIM.TYPE trant
ON metrics.ID1 = trant.BK_TRANSACTIONTYPE

返回报错：

SQL compilation error: syntax error line 3 at position 30 unexpected 'metrics'.

错误原因 #

括号包裹的子查询作为派生表参与关联时，缺少 SELECT * FROM 前缀，SQL解析器无法识别metrics别名的合法来源，触发语法错误。

修复方案 #

方案1：补全派生表查询结构（改动最小） #

SELECT * FROM 
(SELECT *,'{"Source": "010","Keys": [{"Value": "CHARGE"}]}' AS ID1 
FROM DETAILS) metrics
JOIN DIM.TYPE trant
ON metrics.ID1 = trant.BK_TRANSACTIONTYPE

方案2：使用CTE定义临时结果集，可读性更高 #

WITH metrics AS (
    SELECT *,'{"Source": "010","Keys": [{"Value": "CHARGE"}]}' AS ID1 
    FROM DETAILS
)
SELECT * FROM metrics
JOIN DIM.TYPE trant
ON metrics.ID1 = trant.BK_TRANSACTIONTYPE

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