Alias for a CASE WHEN expression in a SELECT cannot be used in GROUP BY?

From: Jining Han <jining.han_at_gmail.com>
Date: 4 Mar 2006 06:56:25 -0800
Message-ID: <1141484185.492840.310220@t39g2000cwt.googlegroups.com>

I need some help explaining the following situation. I have tested the case both in 9i and 10gR2.

Table:

EMPLOYEE

---

 ID                            NUMBER(38)
 SALARY                  NUMBER

Original Query:

---

select
(case when salary>500000 then 'CLASS_A'

else 'CLASS_B' end) emp_class,
sum(salary) Total
from employees
group by
(case when salary>500000 then 'CLASS_A' else 'CLASS_B' end);

Intended Modification:

---

select
(case when salary>500000 then 'CLASS_A'

else 'CLASS_B' end) emp_class,
sum(salary) Total
from employees
group by EMP_CLASS;

The modified query fails with

ERROR at line 6:
ORA-00904: "EMP_CLASS": invalid identifier.

I can create a VIEW
create view emp_class_view as
select
(case when salary>500000 then 'CLASS_A'

else 'CLASS_B' end) emp_class,
salary
from employees

Then the following query works fine:

select emp_class, sum(salary) from emp_class group by emp_class;

I am just having a hard time explaining it.

Thanks a lot
JH Received on Sat Mar 04 2006 - 08:56:25 CST