Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00979: not a GROUP BY expression
ORA-00979: not a GROUP BY expression [message #262176] Fri, 24 August 2007 15:54 Go to next message
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

Re: ORA-00979: not a GROUP BY expression [message #262177 is a reply to message #262176] Fri, 24 August 2007 16:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
Re: ORA-00979: not a GROUP BY expression [message #262178 is a reply to message #262177] Fri, 24 August 2007 16:16 Go to previous messageGo to next message
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
Re: ORA-00979: not a GROUP BY expression [message #262179 is a reply to message #262178] Fri, 24 August 2007 16:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please format your post and put it again with the new sql and the error.

By
Vamsi
Re: ORA-00979: not a GROUP BY expression [message #262180 is a reply to message #262176] Fri, 24 August 2007 16:34 Go to previous messageGo to next message
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
Re: ORA-00979: not a GROUP BY expression [message #262181 is a reply to message #262180] Fri, 24 August 2007 16:55 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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:
  


Current Time: Sat Dec 03 15:49:28 CST 2016

Total time taken to generate the page: 0.11149 seconds