I've been trying to figure out what is wrong with a large sql query that won't accept the change I'm doing.
Have managed to isolate the part that is failing but the error message I get:
ORA-00979: not a GROUP BY expression
is not very helpful.
I suspect there is something fundamentally wrong in what I am trying to do.
I've had to anonimise the table names and fields as the data I'm using is quite sensitive, but this should not detract from the problem.
So here's the query:
select
case when a='100' and cost in (select cost from lookup) then '100' else b end as main,
count(*)
from
data_table
group by
case when a='100' and cost in (select cost from lookup) then '100' else b end
data_table has (amongst others) fields:
a, b, and cost
lookup contains the field:
cost
All fields are varchar2(255)
If I remove the count(*) and entire group by statement the query runs ie:
select
case when a='100' and cost in (select cost from lookup) then '100' else b end as main
from
data_table
This shows the case statement is valid - so why can I not combine the count and group by?
If it is a syntax reason is there an alternative I can use perhaps using subqueries to get around the problem - I would prefer to avoid this and can't really do the case in a decode without it getting messy.
Oracle version:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.
TIA
Edited by: user8378443 on 11-May-2011 10:26