OraFAQ Forum: SQL & PL/SQL » ORA-00979: not a GROUP BY expression

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979: not a GROUP BY expression

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00979: not a GROUP BY expression [message #262176]

Fri, 24 August 2007 15:54

albert357
Messages: 3
Registered: August 2007
Location: NJ

Junior Member

Getting an error with query...can someone please help!

It won't allow me to put a reference to a column in a table?

when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (select d.indust_cd from afs_charges)

1 select d.chrg_key, (case when substr(d.chrg_key,1,1) like 'B%' then ('Billable')
2 when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (select d.indust_cd from afs_charges)
3 when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) not in (select rc_cd from afs_rcs) then ('Client Mktg')
4 when substr(d.chrg_key,1,1) like 'P%' and substr(d.chrg_key,3,4) not in (select rc_cd from afs_rcs) then ('Client Mktg')
5 else ('ADMIN') end),
6 sum(cp.hours)
7 from afs_charges d, afs_cp_detail cp
8 where substr(d.chrg_key,1,6) in ('B00128','M00128','M01A1A')
9 and d.chrg_key = cp.chrg_key
10 group by d.chrg_key, (case when substr(d.chrg_key,1,1) like 'B%' then ('Billable')
11 when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (d.indust_cd)
12 when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) not in (select rc_cd from afs_rcs) then ('Client Mktg')
13 when substr(d.chrg_key,1,1) like 'P%' and substr(d.chrg_key,3,4) not in (select rc_cd from afs_rcs) then ('Client Mktg')
14* else ('ADMIN') end)
16:35:41 SQL>
16:35:41 SQL> /
when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (select d.indust_cd from afs_charges)
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #262177 is a reply to message #262176]

Fri, 24 August 2007 16:01

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

In select clause

select d.chrg_key, (case when substr(d.chrg_key,1,1) like 'B%' then ('Billable')
  when substr(d.chrg_key,1,1) like 'M%' 
   and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs)
  then (select d.indust_cd from afs_charges)

In group by clause

group by d.chrg_key, (case when substr(d.chrg_key,1,1) like 'B%' then ('Billable')
  when substr(d.chrg_key,1,1) like 'M%'
   and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs)
  then (d.indust_cd)

Did you find the difference?

By
Vamsi

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #262178 is a reply to message #262177]

Fri, 24 August 2007 16:16

albert357
Messages: 3
Registered: August 2007
Location: NJ

Junior Member

Hi Vamsi,

I still get the error?

There must be another way around this, I just can't figure it out!

Thanks

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #262179 is a reply to message #262178]

Fri, 24 August 2007 16:31

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Please format your post and put it again with the new sql and the error.

By
Vamsi

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #262180 is a reply to message #262176]

Fri, 24 August 2007 16:34

albert357
Messages: 3
Registered: August 2007
Location: NJ

Junior Member

  1  select d.chrg_key, case when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (d.indust_cd) end,
  2  sum(cp.hours)
  3  from afs_charges d, afs_cp_detail cp
  4  where substr(d.chrg_key,1,6) in ('B00128','M00128','M01A1A')
  5  and d.chrg_key = cp.chrg_key
  6* group by d.chrg_key, case when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (d.indust_cd) end
17:20:22   7  
17:20:22 SQL> /
select d.chrg_key, case when substr(d.chrg_key,1,1) like 'M%' and substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs) then (d.indust_cd) end,
                                                                                                                              *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

Report message to a moderator

Re: ORA-00979: not a GROUP BY expression [message #262181 is a reply to message #262180]

Fri, 24 August 2007 16:55

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Quote:

substr(d.chrg_key,3,4) in (select rc_cd from afs_rcs)

is giving problem.

You can use outer join with the table afs_rcs.

By
Vamsi

[Updated on: Fri, 24 August 2007 17:00]

Report message to a moderator

Previous Topic:	Join or subquery, which is faster
Next Topic:	CHANGE THE LEVEL NUMBER IN AN SQL QUERY (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:04:58 CST 2024