透视层级表后左连接报错：无效标识符a.level，求排查修正

错误原因 #

1. PIVOT别名语法错误：原SQL中PIVOT部分错误地使用了两次别名（AS HierTable 和 as a），导致a这个别名没有正确绑定到PIVOT后的结果集，数据库无法识别a."Level"字段。
2. 冗余CTE：定义了HierCTE但未在后续查询中使用，属于无效代码。
3. 拼写错误：Temp_table_2中的字段"vaule1"拼写错误，应为"value1"。
4. 连接方式不规范：使用逗号隐式交叉连接TABLE(SPLIT_TO_TABLE(...))，容易引发歧义，建议显式使用CROSS JOIN。

修改后的SQL #

SELECT * FROM
  (SELECT "NodeName","ParentId","Level",INDEX, MAX(VALUE) OVER 
  (PARTITION BY "Level", INDEX) CT,PATH FROM
  (SELECT SYS_CONNECT_BY_PATH("Level", '\\\\') 
  PATH,"NodeName","ParentId","Level" FROM Temp_table_1
    start with "ParentId" is not null connect by
    prior "Level" = "ParentId")
  CROSS JOIN TABLE(SPLIT_TO_TABLE(PATH, '\\\\')))

PIVOT(MAX(CT) FOR INDEX IN (2,3,4,5,6,7,8,9,10,11,12)) AS a
("NodeName","ParentId","Level",PATH,"Level1","Level2","Level3",
 "Level4","Level5", "Level6","Level7","Level8","Level9","Level10", "Level11")

LEFT JOIN
(SELECT 
 "Col1" AS "Level",
 TRY_CAST("value1" AS INT ) AS "NewValue1",
 TRY_CAST("value2" AS INT) AS "NewValue2"
FROM Temp_table_2
WHERE "class1" = 'AAAA') as b
ON b."Level" = a."Level"

关键修改点说明 #

• 修正PIVOT别名：将AS HierTable (...) as a调整为AS a (...)，确保a正确关联PIVOT结果集。
• 移除未使用的HierCTE，精简代码。
• 修正vaule1为value1，避免字段识别错误。
• 用CROSS JOIN替代逗号隐式连接，提升代码可读性与规范性。

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