Cannot enclose a query with an outer query in Firebird 2.5

Question

My inner query is returning results but I get this error

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]

Here is my query

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

Answer 1

Your derived table has two columns with the name/alias NOME (m.nome and PS.NOME), and as the error indicates, this is not allowed as a name should occur only once. You need to alias one or both columns so they have unique names, or exclude one of them if it would have the same value. The same was already done for the third occurrence of NOME, GA.NOME.

For example:

select * from (
  
SELECT t.placa, 
   m.nome as m_nome, 
   GA.ID_AUDESP, 
   GA.NOME AS GRUPO_AUDESP, 
   T.VALOR as valor,
   T.DT_AQUISICAO,
   PS.NOME as ps_nome
FROM PATRIMONIO_TOMBAMENTO T 
-- ...