Calculated result in Firebird SELECT

Question

Can anyone explain why this works:

Select C1, C2, C1 + C2 as C3
from V1,V2
where C2 > 0

and

Select C1, C2, C1 + C2 as C3
from V1,V2
where C3 > 0

gives the following error:

*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*C3
*At line 1, column 53
caused by
'isc_dsql_prepare'

This is using the Firebird version with LibreOffice 6.1.3

Answer 1

You cannot use aliases from the SELECT column list in the WHERE-clause: you need to use the original column; the column list of a select is evaluated after the where. In other words, you'll need to use where C1 + C2 > 0

Alternatively, you need to use a sub-query:

select * 
from (Select C1, C2, C1 + C2 as C3 from V1,V2) a
where C3 > 0 

And I strongly suggest that you start using SQL-92 explicit joins, as those are for more readable than SQL-89 implicit joins.