Postgres Pro
Downloads
Home   >   mailing lists

Thread: alias question

alias question

From
Ceasar
Date:
I was wondering if you could use an alias to do math calculations:  for
example,

select Bank1,
(var1 + var2) as calc,
calc*100 as var2

From......

I keep getting this message that says that calc is not in any of the
tables in the From statement. Is it possible to use a alias (calc) to
make further calculations?  If so, how?  Any help would be greatly
appreciated.

ocerda@hotmail.com

Re: alias question

From
Stephan Szabo
Date:
On Thu, 13 Dec 2001, Ceasar wrote:

> I was wondering if you could use an alias to do math calculations:  for
> example,
>
> select Bank1,
> (var1 + var2) as calc,
> calc*100 as var2
>
> From......
>
> I keep getting this message that says that calc is not in any of the
> tables in the From statement. Is it possible to use a alias (calc) to
> make further calculations?  If so, how?  Any help would be greatly
> appreciated.

I don't believe directly.  You can fake this somewhat by making a two
level query where the calculation is done in a subselect in from. It
looks however like the above is recursive, var2 depends on calc and calc
depends on var2 (or is var2 also a column name?)
Assuming it is a column, you should be able to do something like

select Bank1, calc, calc*100 as var2 from
 (select bank1, (var1+var2) as calc from ... ) ali;

Re: alias question

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Assuming it is a column, you should be able to do something like

> select Bank1, calc, calc*100 as var2 from
>  (select bank1, (var1+var2) as calc from ... ) ali;

This should work for saving typing, but be aware that it doesn't
necessarily save any computation.  The planner will likely try to
collapse the two selects together, thus rewriting into the form

select bank1, (var1+var2) as calc, (var1+var2)*100 as var2 from ...

This shouldn't be an issue unless there is a hugely expensive function
or sub-select involved in the expression.  In that case you might want
to use a temp table for the results of the lower select, and then do the
upper select from the temp table, so that the planner can't fold them
together.

            regards, tom lane