Subject | sql question. |
---|---|

Author | sugi |

Post date | 2004-01-17T21:07:13Z |

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.

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.