group by behavior in 11gR2
Date: Wed, 26 Oct 2011 13:57:43 -0700
I noticed this in 11gR2. The query here does not mean anything, but I just ran it to show the difference.
select end_date, kumar, user_name, employee_id, max(end_date) from (select end_date||user_name||employee_id as kumar, end_Date, user_name, employee_id from fnd_user)
group by end_date, user_name, employee_id /
ERROR at line 1:
ORA-00979: not a GROUP BY expression
But the same query would work in 18.104.22.168 for example.
To make it work in 22.214.171.124 , I have to group by kumar as well.
I see some workarounds in the form of changing optimizer features enabled and some other _ parameters can be set/unset.But my question is why is this change in behavior. Looks like now the 'virtual columns' also need to be included in the group by clause.