group by behavior in 11gR2
From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Wed, 26 Oct 2011 13:57:43 -0700
Message-ID: <CAHDOOG4tTCHP2TUKmeRcfKO5BOjy9Gs0s1mjjGLVfxaeK9Kn5w_at_mail.gmail.com>
Hi
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 /
Date: Wed, 26 Oct 2011 13:57:43 -0700
Message-ID: <CAHDOOG4tTCHP2TUKmeRcfKO5BOjy9Gs0s1mjjGLVfxaeK9Kn5w_at_mail.gmail.com>
Hi
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 11.1.0.7 for example.
To make it work in 11.2.0.2 , 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.
Thank you
Kumar
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 26 2011 - 15:57:43 CDT