时间戳

首页 > 解决方案 > 带有别名的子查询失败，标识符为“T1.C0”

snowflake-cloud-data-platform - 带有别名的子查询失败，标识符为“T1.C0”

问题描述

SELECT t1.c0, t1.c1::VARCHAR(16777216), t1.c2::VARCHAR(16777216), t1.c3::VARCHAR(16777216), t1.c4::VARCHAR(16777216) FROM (SELECT (t0."PERSONID"::NUMBER(38, 0))::DOUBLE, t0."LASTNAME"::VARCHAR(255), t0."FIRSTNAME"::VARCHAR(255), t0."ADDRESS"::VARCHAR(255), t0."CITY"::VARCHAR(255) FROM "PERSONS_SNW" AS t0) AS t1 (c0, c1, c2, c3, c4) WHERE c0 = 1

上面的 sql 因 SQL 编译错误而失败：位置 7 处的错误行 1 无效标识符 'T1.C0' 为什么我们不能将选择别名附加到列别名中？

SELECT c0, c1::VARCHAR(16777216), c2::VARCHAR(16777216), c3::VARCHAR(16777216), c4::VARCHAR(16777216) FROM (SELECT (t0."PERSONID"::NUMBER(38, 0))::DOUBLE, t0."LASTNAME"::VARCHAR(255), t0."FIRSTNAME"::VARCHAR(255), t0."ADDRESS"::VARCHAR(255), t0."CITY"::VARCHAR(255) FROM "PERSONS_SNW" AS t0) AS t1 (c0, c1, c2, c3, c4) WHERE c0 = 1

标签： snowflake-cloud-data-platform

解决方案

似乎 Snowflake 无法处理子查询的列别名（目前）。也许您可以提交有关它的支持票。不确定这是一个错误还是故意不支持。

-- not working
SELECT t1.c0 FROM 
(SELECT v1,v2 from testy) as t1(c0, c1);

-- working if you define alias in subquery
SELECT t1.c0 FROM 
(SELECT v1 c0, v2 from testy) as t1;

-- working if you use CTE instead of subquery
with t1 (c0, c1)  as 
(SELECT * from testy)
SELECT t1.c0 FROM t1;

您可以在子查询中定义别名，也可以使用 CTE 代替子查询。

推荐阅读