Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: DECODE
Chua Boon Yiang <chuaby_at_hotmail.com> wrote in message
news:87do47$k2t$1_at_newton.pacific.net.sg...
> Hi,
> in the statement :
> select
> DECODE (deptno,10, 'ACCOUNTING',
> 20, 'RESEARCH',
> 30, 'OPERATION',
> 40, 'OPERATION',
> 'NONE')
> count(*) from tableA
> group by deptno
>
> is there anyway for me to group by the decoded values rather than
> the original stored values ?
> eg i have 2 diff dept numbers, but i want to group them as 1 dept,
> using the above statement, there will be 2 rows of OPERATION.
> col alias cannot be used in the group by.
You'd copy full decode call to group by clause. Just like this:
select
DECODE (deptno,10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'OPERATION', 40, 'OPERATION', 'NONE') count(*) from tableA group by DECODE (deptno,10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'OPERATION', 40, 'OPERATION', 'NONE')
If You want avoid code copying, You'd use subquery:
select dep_name, count(*) from
(
select
DECODE (deptno,10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'OPERATION', 40, 'OPERATION', 'NONE') as dep_namefrom tableA
But note that indexes can't be used in the both cases. I suppose You'd better create depatments table with code and name columns.
--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)
Igor V. Podolsky (igoryok_at_soft-review.kiev.ua) Received on Fri Feb 04 2000 - 04:54:16 CST
![]() |
![]() |