10 Replies Latest reply: May 15, 2012 11:37 PM by 909064 RSS

    "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).

    909064
      Hi,

      We have a query which is working in 10g and giving error "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3). we have found following two solution to resolve it from the internet.

      1)You can Set the hidden parameter “_FIX_CONTROL”=’5520732:OFF’ in the pfile. This parameter disables a given bug number.

      2)We can set the parameter optimizer_features_enable to a previous version of Oracle, ’11.1.0.7′

      but I am unable to find the side effects of above change, so I need to know:
      1)whcih is batter solution
      2)what are the possible side effects of both solution.
        • 1. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
          Fran
          Could you provide us the query? it is exactly the same like in 10g database?
          • 2. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
            900442
            How about raising an SR if possible..

            Regards...
            • 3. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
              909064
              Hi Fran,
              Please find the below query.

              SELECT DECODE(TEMP.TENOR_IND, 'L', TEMP.SHORT_NAME, NULL) PRODUCT_NAME,
              TEMP.BA_PRODTYPE_ID,
              TEMP.TENOR,
              TEMP.PERIOD_TYPE,
              TEMP.PERIOD_VALUE,
              SUM(TEMP.LIMIT_BASE_AMOUNT),
              SUM(TEMP.PNDG_LIMIT_BASE_AMOUNT),
              SUM(TEMP.GROSS_EXP_BASE_AMOUNT),
              SUM(TEMP.NETT_EXP_BASE_AMOUNT),
              SUM(TEMP.COLLATERAL_BASE_AMOUNT),
              TEMP.TENOR_IND,
              MAX(TEMP.COB_DATE),
              TEMP.CRP_PRODUCT_GROUP
              FROM (SELECT /*+INDEX(LIM BUS_CB_LIMIT_INDX01) INDEX(btpd BUS_TP_PORTFOLIO_DTLS_INDX02)*/
              MCBPT.BA_PRODTYPE_ID,
              DECODE(LIM.TENOR_IND, 'L', MCBPT.SHORT_NAME, NULL),
              LIM.LIMIT_ID,
              LIM.LIMIT_TYPE_ID,
              LIM.LIMIT_STATUS,
              LIM.OWNER_ID,
              LIM.PERIOD_TYPE,
              LIM.PERIOD_VALUE,
              LIM.LIMIT_CURR,
              LIM.LIMIT_BASE_AMOUNT,
              LIM.GROSS_EXP_BASE_AMOUNT,
              DECODE(LIM.PERIOD_TYPE,
              5,
              'Inf',
              (TO_CHAR(LIM.PERIOD_VALUE) || '-' ||
              DECODE(LIM.PERIOD_TYPE, 4, 'Y', 3, 'M', 2, 'W', 1, 'D'))) TENOR,
              LIM.TENOR_SET_ID,
              LIM.PNDG_LIMIT_CURR,
              LIM.PNDG_LIMIT_BASE_AMOUNT,
              LIM.NETT_EXP_BASE_AMOUNT,
              LIM.COLLATERAL_BASE_AMOUNT,
              LIM.COB_DATE,
              LIM.TENOR_IND,
              MCBPT.CRP_PRODUCT_GROUP,
              MCBPT.SHORT_NAME
              FROM BUS_CB_LIMIT LIM,
              BUS_TP_PORTFOLIO_DETAILS BTPD,
              MST_CB_BA_PRODUCT_TYPE MCBPT
              WHERE BTPD.ORG_ID = 108503
              AND EXISTS
              (SELECT ORG_ID
              FROM BUS_CA_CORPORATE
              WHERE ORG_STAT IN (1, 2, 4, 7, 8, 9, 22, 24, 25)
              AND BTPD.BOOKING_ENTITY = ORG_ID
              START WITH ORG_ID = 894
              CONNECT BY PRIOR ORG_ID = PARENT_ORG_ID)
              AND NOT EXISTS
              (SELECT /*+INDEX(btpd1 BUS_TP_PORTFOLIO_DTLS_INDX02)*/
              btpd1.ba_prodtype_id
              FROM bus_tp_portfolio_details btpd1
              WHERE btpd1.org_id = 108503
              AND btpd1.booking_entity = 894
              AND BTPD.BA_PRODTYPE_ID = btpd1.ba_prodtype_id
              AND EXISTS
              (SELECT ba_prodtype_id
              FROM mst_cb_ba_product_type
              WHERE product_grp_id = 1
              AND btpd1.ba_prodtype_id = ba_prodtype_id))
              AND MCBPT.BA_PRODTYPE_ID = BTPD.BA_PRODTYPE_ID
              AND BTPD.CP_STATUS = 4
              AND LIM.OWNER_ID = BTPD.PORTFOLIO_ID
              AND LIM.OWNER_TYPE = 4
              AND LIM.LIMIT_TYPE_ID = 2) TEMP
              WHERE 1 = 1
              GROUP BY TEMP.BA_PRODTYPE_ID,
              TEMP.PERIOD_TYPE,
              TEMP.PERIOD_VALUE,
              TEMP.TENOR_IND,
              TEMP.SHORT_NAME,
              TEMP.CRP_PRODUCT_GROUP
              ORDER BY TEMP.BA_PRODTYPE_ID, TEMP.PERIOD_TYPE, TEMP.PERIOD_VALUE
              • 4. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                713555
                definitely raise an SR. thats not right but its known issue so you will have found on support if you search or open sR.

                If you dont have support, try those parameters you mention, cant really be any worse off, fix_control was brought in to bypass bugs but it is reported that it can prevent databse from starting as its hidden param and usually should only be used on request of support. but the database will either start or it wont. so you'll know very quicikly if you put it in.

                optimizer_features_enable is safer but if you do use it how do you know the optimizer wont change if you use at system level for prior version. I would suggest set online trigger for online user to do alter session for that.
                • 5. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                  Dom Brooks
                  We have a query which is working in 10g and giving error "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3). we have found following two > solution to resolve it from the internet.
                  1)You can Set the hidden parameter “_FIX_CONTROL”=’5520732:OFF’ in the pfile. This parameter disables a given bug number.
                  2)We can set the parameter optimizer_features_enable to a previous version of Oracle, ’11.1.0.7′
                  but I am unable to find the side effects of above change, so I need to know:
                  1)whcih is batter solution
                  2)what are the possible side effects of both solution.
                  I would try to match the scope of the solution to the scope of the problem.

                  If he problem is a single query, the solution should ideally impact only that query.

                  Both the fix control and the optimizer_features_enable solutions could be implemented at the session level using ALTER SESSION in your code or just at the query level using the /*+ OPT_PARAM */ hint.

                  But I would definitely raise an SR and if patching is viable then consider patching. But bear in mind that patches can introduce other bugs which is exactly what fix control 5520732 seems to have done for you.
                  • 6. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                    Fran
                    are you sure that is the same query that are running in your database 10g?

                    I don't see TEMP.TENOR in Group By clause, could you check it please?
                    • 7. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                      909064
                      Yes Fran,

                      It is running in 10g.
                      • 8. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                        fjfranken
                        Hi,


                        We have similar issue over here, in a 11.2.0.3 database on AIX7.1.
                        Oracle Support first told us that the problem is solved in 11.2.0.3 ( according to bug ID 9824198), so it took me some effort and a lot of test-cases from them to show them the problem still does exist.
                        Meanwhile Development is working already for more than 2 months on a solution.

                        Extra info: Our Bug is referred to by Bug number 13622036

                        Regards,
                        FJFranken
                        MyBlog: http://managingoracle.blogspot.com

                        Edited by: fjfranken on 17-apr-2012 0:43
                        • 9. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                          713555
                          To add to above, I would have to wonder about the latest release bug fixes, I have had 2 cases in the last 3 months where I was told bugs in 11.2.0.2.0 were fixed in 11.2.0.3 but werent and like above, had to produce test cases to prove. This is not behaviour Ive seen before from support, ie, usually if they say a fix is out, its out (milage may vary between posters). Only anecdotal story but one that should be watched.
                          • 10. Re: "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (11.2.0.3).
                            909064
                            Thanks Dom Brooks for your help