Using Column aliases in Calculations

[memarkiam]

I thought I could use 'as' to name a column, and then use the column name in calculations. This would be particualry useful for aggregated functions. But I can't get it to work - I get 'invalid column name' error (on Oracle 8i).

If anyone has any suggestions, that would be great!
Thanks,
Mark

I'm trying this query:

select dm2_emp_location.CONTROL_6 as Store,
count(case when DM2_EMPLOYEE.DATE_OF_TERM is not null then DM2_EMP_location.EMPLOYEE_NUMBER end) as HeadCount,
count(case when DM2_EMPLOYEE.EMPLOYMENT_DATE >='1-Jan-2001' then DM2_EMPLOYEE.EMPLOYMENT_DATE end) as Starters,
count(case when DM2_EMPLOYEE.DATE_OF_TERM >='1-Jan-2001' then DM2_EMPLOYEE.DATE_OF_TERM end) as Leavers,
Leavers/HeadCount * 100 as Churn,
avg(case when DM2_EMPLOYEE.DATE_OF_TERM >='1-Jan-2001' then trunc(months_between(DM2_EMPLOYEE.DATE_OF_TERM, DM2_EMPLOYEE.EMPLOYMENT_DATE),2) end) as retention
from DM2_EMPLOYEE, dm2_emp_location
where dm2_emp_location.EMPLOYEE_NUMBER = DM2_EMPLOYEE.EMPLOYEE_NUMBER
group by dm2_emp_location.CONTROL_6

 

[Guest_imported]

mark i suppose the alias names and further operation on them can only be done if u create a view with those alias
as the column names...
if u create a view for the table giving alias name...like store,headcount..then u'll be able to process them

hopefully u'll be able to get the results right.

Best of luck!!