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

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sat, 4 Mar 2006 11:17:04 -0500
Message-ID: <9oOdnRfnTuMfIJTZRVn-iA@comcast.com>

"Jining Han" <jining.han_at_gmail.com> wrote in message news:1141484185.492840.310220_at_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
:

explanation: that's the way it works, no column aliases in the group by:

SQL> select deptno as dept_id, sum(sal)
  2 from emp
  3 group by dept_id;
group by dept_id

         *
ERROR at line 3:
ORA-00904: "DEPT_ID": invalid identifier

but you could use an in-line view (which makes more sense with you CASE expression than my simple alias example)

SQL> select dept_id, sum(sal)
  2 from (

  3     select deptno as dept_id, sal
  4     from   emp

  5 )
  6 group by dept_id;

   DEPT_ID SUM(SAL)
---------- ----------

        10    8025.32
        20   17172.41
        30       9330

++ mcs Received on Sat Mar 04 2006 - 10:17:04 CST