You can't reference a derived column in the where clause. Most
credible database engines will enforce this rule. The WHERE clause
is processed before the SELECT clause, so your derived columns would
not be known to the WHERE clause.

The calculation would be processed twice for any rows in your
resultset, but not for every row in your table. For instance, if
there are 10,000 rows in your table, but only 100 rows satisfy the
condition in your WHERE clause, then the WHERE clause will execute
the calculation 10,000 times to determine which rows satisfy the
condition, but the calculation in the SELECT clause will only be
executed 100 times.

The VIEW is a good idea. The only other workaround I can think of
is a computed column. I haven't done much with computed columns in
Firebird. However, if Firebird allows you to create in index on the
computed column, then you will realize a big performance increase.
This is because your current query will always do a full table scan,
which is bad for performance. With an index, you avoid a full table
scan. The query plan will show this to you.

Bill

--- In firebird-support@yahoogroups.com, sugi <truesaint@c...> wrote:
> 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.