Firebird SQL statement with UNION gives data type unknown even with cast

Question

I have the following query where in one of the union queries, a field has to be 0, and in the other part it gives an average from a subquery. I have to use a SELECT subquery. I can not use JOINS as it will yield doubled records in the result set (the actual query is far more complex):

SELECT A,B, CAST(0 as FLOAT) as C
 FROM TABLE1
 WHERE CONDITION=0
UNION
SELECT T1.A, T1.B, (SELECT AVG(T3.C+T3.E) FROM T3 WHERE T3.A = T1.A) as C
 FROM TABLE1 T1
 WHERE T1.CONDITION=1

All fields are Float types in the database, but I still get data type unknown.

Is there a way to know which data type AVG(T3.C+T3.E) has?

My workaround is to use (T1.C*0) as C instead of CAST(0 as FLOAT) as C.

Answer 1

One method is to avoid the union at all:

SELECT A, B,
       (CASE WHEN CONDITION = 0 THEN CAST(0 as FLOAT)
             ELSE (SELECT AVG(T3.C+T3.E) FROM T3 WHERE T3.A = T1.A) 
        END) as C
FROM TABLE1
WHERE CONDITION IN (0, 1);