creating column names and using them

[sagn]

I want to use a column name I have created in a select
statement. I'm pretty sure it can be done but I
seem to be doing something wrong. (I've seen people
use it here...)

For example;

select apples a,bees b, a + b from table

the table looks like

apples bees
1 100
2 500

But I get an error saying a is an invalid column name.
(b is also invalid)

How can I do this?

Thanks

ds

 

[redlam]

I think you're confusing table alias with column names here. The following should work but its not exactly what you wanted:

select t.apples,
t.bees,
t.apples + t.bees as applebees
from table t

 

[redlam]

You can also use column aliases but I'm still not sure its what you're after...

select apples as a,
bees as b,
apples + bees as ab
from table

 

[sagn]

No, I really want to be able to use
the new column names so that I do not
have to re-enter an expression everytime I want to use it.
I thought it was possible when

John Fill responded to one of my posts and used
a similar syntax. His response is below. Perhaps
he was just using short hand and I misunderstood.

BEGIN SQL STMT

select a,b,c,count(*) cnt,sum(d),(select count(*) from table_1 t where t.a = table1.a) count_of_a, (select count(*) from table_1) count_all, ((count_of_a/count_all)*100) percentaje_of_a
from table_1
group by
a,b,c

John Fill


END

 

[redlam]

Hmmm... hard to say. Looks to me like John was just using columns that were already named 'a' 'b' and 'c' but I can't be sure. He's around... maybe he can clarify. This would be a really handy thing - I've probably wasted much typing if this is possible.

 

[sagn]

Yeah. It would be sooo sweet.

ds