Re: Referencing an expression

On Tue, 2008-08-19 at 09:36 -0700, Dispensa, Brad wrote:

> My question is, in an access query you can define a query expression
> and then reference that expression again later in the query:
> Example
> Select 1_quantity, 1_cost,
> [1_quantity]*[cost] as 1_subtotal,
> 2_quantity, 2_cost,
> [2_quantity]*[cost] as 2_subtotal,
> [1_subtotal]+[ 2_subtotal] as total
> From table

I think your options are

1) inline
select q1,c1,q1*c1 as e1, q2,c2,q2*c2 as e2, q1*c1+q2*c2 as total from
items

2) subquery
select ec.*,e1+e2 as total from (select q1,c1,q1*c1 as e1, q2,c2,q2*c2
as e2 from items) ec;

3) view
create view extended_costs as select q1,c1,q1*c1 as e1, q2,c2,q2*c2 as
e2 from items;
select *,e1+e2 as total from extended_costs;

If postgresql optimizes common subexpressions like q1*c1, the
performance difference between inline and subquery will be negligible
(and probably even if it doesn't). I'm fairly certain that there's no
performance difference between the subquery and view.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0