Dear all,

I have a query like this :
...
select t.*,
Coalesce(((t.data1 + t.data2) * (t.data3 - t.data4)), 0)
as FINAL_VALUE
from myTable t;
...

In another place, I need a similar query, but only for (FINAL_VALUE >=
100). Modifying the query to the one below doesn't work, with
COLUMN_UNKNOWN error (FINAL_VALUE).
...
select t.*,
Coalesce(((t.data1 + t.data2) * (t.data3 - t.data4)), 0)
as FINAL_VALUE
from myTable t
where (FINAL_VALUE >= 100);
...

The only way i can make the query to work is by duplicating the whole
calculation on the where clause. Works, but looks ugly.
...
select t.*,
Coalesce(((t.data1 + t.data2) * (t.data3 - t.data4)), 0)
as FINAL_VALUE
from myTable t
where
(Coalesce(((t.data1 + t.data2) * (t.data3 - t.data4)), 0)) >= 100;
...


Some questions:
1. Performance: does firebird execute the calculation twice for each
row? I'm concerned because there might be a possibility that the
calculation will get more and more complex, even involving subqueries
from another table in the future.
2. Is there a better way to express the above query ?

Thank you very much in advance,
sugi.


PS:
Is there any way to basically say 'select * from bla_bla_bla where
the_third_column is > 100' ? Something similar with the 'ORDER BY N' trick?

I don't think so, since it might lead to the question of
interpreting/differentiating the following queries,
...
select * from table where (3 >= 100); //constant to constant comparison.
vs
select * from table where (COLUMN(3) >= 100);
...
but I would appreciate any insights on this.