How to avoid Interbase error 'Column Unknown'

Question

There are 2 tables in InterBase 2020 db: t1 and t2.

t1 fields: i1 integer,
           f1 float
t2 fields: i2 integer,
           f2 float 

Derived table (dt1) works properly on IB and on FB, but this full code returns an error only on IB server:

'Column Unknown F1'

select
    i1 as i3,
    sum(f1) as f3
    
From
      (select
        i1,
        sum(f1) as f1
         from t1
          group by i1
    
        union all
    
       select
        i2,
        sum(f2) as f2
         from t2
          group by i2) dt1
 group by i1

How to avoid this error?

Answer 1

Your outer query has only knowhow of your derived table column names. So, qualify your column names during the derived table definition of "dt1", as such; "dt1 (i1, f1)".

select
    i1 as i3,
    sum(f1) as f3
    
From
      (select
        i1,
        sum(f1) as f1
         from t1
          group by i1
    
        union all
    
       select
        i2,
        sum(f2) as f2
         from t2
          group by i2) dt1 (i1, f1)
 group by i1;