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

 

[PruSQLer]

The alias name of a column is really only useful in report output. Your database can't do the calculation you're asking it to do because because the query is processing on a row by row premise, not an accumulation basis. I would use a temporary table to compile the values, then do the calculations in a subsequent query. If you can't do temporary tables, you could use a sub query something like this:

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,
(select count(case when DM2_EMPLOYEE.DATE_OF_TERM is not null then DM2_EMP_location.EMPLOYEE_NUMBER end)
from DM2_EMPLOYEE, dm2_emp_location
where dm2_emp_location.EMPLOYEE_NUMBER = DM2_EMPLOYEE.EMPLOYEE_NUMBER
)
/
(Select count(case when DM2_EMPLOYEE.DATE_OF_TERM >='1-Jan-2001' then DM2_EMPLOYEE.DATE_OF_TERM end)
from DM2_EMPLOYEE, dm2_emp_location
where dm2_emp_location.EMPLOYEE_NUMBER = DM2_EMPLOYEE.EMPLOYEE_NUMBER) ,

etc, etc
I'm not sure I got your fields all ordered correctly but that's the general idea.