SQL报错Invalid call to qualifier on unresolved object，求排查解决

• 表名拼写错误：在child_A的id字段计算逻辑中，你引用了不存在的表parent_star_db，正确的目标表应该是CTE中定义的parent_A。这个笔误导致SQL解析器无法识别目标对象，直接触发报错。
• 关联逻辑与错误的叠加影响：当你把r.id替换为固定值0时，SQL优化器的执行路径发生变化，暂时绕过了对错误表引用的严格校验，但这只是掩盖了核心问题，并非真正解决。

修正child_A中id字段子查询的表名，将parent_star_db改为parent_A即可解决报错：

WITH parent_A AS (
        SELECT 
              ROW_NUMBER() OVER (ORDER BY UPPER(specialty))+2 AS id
            , 2                                             AS parent_id
            , 'A'                                           AS type
            , specialty                                     AS name 
            , '0'                                           AS is_root  
            , '0'                                           AS is_leaf
            , NULL                                          AS dept_id 
            , CONCAT('A_' , specialty)                      AS label  
        FROM  (  SELECT DISTINCT specialty FROM tmp ORDER BY 1) as w 
), child_A AS (
    SELECT
           -- 修正表名为parent_A
           ROW_NUMBER() OVER (ORDER BY UPPER(t.dept_name))+(SELECT MAX(id) FROM parent_A) AS id
          , r.id                                                               AS parent_id
          , 'A'                                                                AS type
          , t.dept_name                                                        AS name 
          , '0'                                                                AS is_root  
          , '1'                                                                AS is_leaf  
          , t.data_c                                                           AS dept_id  
          , CONCAT('A_' , t.data_c , '_' , t.dept_name)                        AS label
    FROM tmp as t
    JOIN parent_A as r ON t.specialty = r.name
)


SELECT  * FROM parent_A
UNION ALL
SELECT  * FROM child_A

如果需要进一步优化MAX(id)的计算效率，部分SQL方言支持将其提前存入变量，但上述修改已经能解决核心报错问题。

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