Firebird 2.5嵌套查询报错：派生表EX存在重复列NOME

错误信息 #

SQL Error [336397221] [42000]: Dynamic SQL Error; SQL error code =-104; Invalid command; column NOME was specified multiple times for derived table EX [SQLState:42000, ISC error code:336397221]

原查询语句 #

select * from ( --this gives error
  
SELECT t.placa, m.nome, GA.ID_AUDESP, GA.NOME AS GRUPO_AUDESP, 
T.VALOR  as valor,

   T.DT_AQUISICAO,
     PS.NOME

FROM PATRIMONIO_TOMBAMENTO T 
LEFT JOIN PATRIMONIO_GRUPO_AUDESP GA ON GA.ID_GRUPO_AUDESP = T.ID_GRUPO_AUDESP
LEFT JOIN ESTOQUE_MATERIAL M ON M.ID_MATERIAL = T.ID_MATERIAL 
LEFT JOIN PATRIMONIO_SETOR PS ON (T.ID_SETOR = PS.ID_SETOR)
WHERE T.ID_ORGAO = '030000'
AND (T.SITUACAO IN('A') or ( T.SITUACAO = 'B' AND T.DT_BAIXA >'2022-01-31'))
 AND (T.DT_REATIVADO IS NULL OR T.DT_REATIVADO<= '2022-01-31' or (T.DT_BAIXA >'2022-01-31'))
AND T.dt_cadastro <= '2022-01-31'
) ex

解决方法 #

报错原因是内层查询返回了两个同名的NOME列，分别来自ESTOQUE_MATERIAL和PATRIMONIO_SETOR表。派生表不允许存在重复列名，因此需要给这两个列分别指定唯一别名：

select * from (
  
SELECT t.placa, m.nome AS MATERIAL_NOME, GA.ID_AUDESP, GA.NOME AS GRUPO_AUDESP, 
T.VALOR  as valor,

   T.DT_AQUISICAO,
     PS.NOME AS SETOR_NOME

FROM PATRIMONIO_TOMBAMENTO T 
LEFT JOIN PATRIMONIO_GRUPO_AUDESP GA ON GA.ID_GRUPO_AUDESP = T.ID_GRUPO_AUDESP
LEFT JOIN ESTOQUE_MATERIAL M ON M.ID_MATERIAL = T.ID_MATERIAL 
LEFT JOIN PATRIMONIO_SETOR PS ON (T.ID_SETOR = PS.ID_SETOR)
WHERE T.ID_ORGAO = '030000'
AND (T.SITUACAO IN('A') or ( T.SITUACAO = 'B' AND T.DT_BAIXA >'2022-01-31'))
 AND (T.DT_REATIVADO IS NULL OR T.DT_REATIVADO<= '2022-01-31' or (T.DT_BAIXA >'2022-01-31'))
AND T.dt_cadastro <= '2022-01-31'
) ex

修改后内层查询的列名全部唯一，外层select *即可正常执行。

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