Solved

ORA-00937 error

Posted on 2010-11-10
4
503 Views
Last Modified: 2012-05-10
I have this query that is running fine in our QA/UAT/Prod environment but fails in the Dev environment.  Can anybody tell me why that is?

SELECT fc.rep_period_start_dt start_date,
            fc.rep_period_end_dt end_date,
            fc.benefit_compliance_group benefit_compliance_group,
            SUM (fc.total_amount) amount,
            (CASE
                WHEN SUM ( (SUM (fc.total_amount))) = 0
                THEN
                   0
                ELSE
                   (SUM (fc.total_amount))
                   / SUM ( (SUM (fc.total_amount))) OVER ()
             END)
               TOT_PERCENT
       FROM saas.fact_compliance fc
      WHERE fc.transaction_group_code = 'SALE' AND fc.benefit_status = 'A'
            AND fc.benefit_compliance_group IN
                     ('GRO',
                      'EAB',
                      'GMWB',
                      'IAB',
                      'LT5',
                      'GMDB',
                      'GMIB',
                      'HDV',
                      'HD',
                      'HDGRO')
            AND fc.transaction_count > 25
            AND (fc.contract_issue_date BETWEEN (SELECT REP_PERIOD_START_DT
                                                   FROM SAAS.FACT_COMPLIANCE
                                                  WHERE REP_PERIOD_START_DT IS NOT NULL
                                                        AND ROWNUM = 1)
                                            AND  (SELECT REP_PERIOD_end_DT
                                                    FROM SAAS.FACT_COMPLIANCE
                                                   WHERE REP_PERIOD_END_DT IS NOT NULL
                                                         AND ROWNUM = 1))
   GROUP BY fc.rep_period_start_dt,
            fc.rep_period_end_dt,
            fc.benefit_compliance_group;

SELECT fc.rep_period_start_dt start_date,
       *
ERROR at line 1:
ORA-00937: not a single-group group function

The Oracle versions of the database are

Dev = 10.2.0.5 (was just upgraded)
QA/UAT/Prod are 10.2.0.1

Is there an issue with 10.2.0.5?
0
Comment
Question by:BadHatHarry
  • 2
  • 2
4 Comments
 
LVL 13

Expert Comment

by:dsmile
ID: 34109187
http://ora-00937.ora-code.com/
SELECT fc.rep_period_start_dt start_date,

            fc.rep_period_end_dt end_date,

            fc.benefit_compliance_group benefit_compliance_group,

            SUM (fc.total_amount) amount,

            (CASE

                WHEN SUM ( (SUM (fc.total_amount))) = 0

                THEN

                   0

                ELSE

                   (SUM (fc.total_amount))

                   / SUM ( (SUM (fc.total_amount))) OVER ()

             END)

               TOT_PERCENT

       FROM saas.fact_compliance fc

      WHERE fc.transaction_group_code = 'SALE' AND fc.benefit_status = 'A'

            AND fc.benefit_compliance_group IN

                     ('GRO',

                      'EAB',

                      'GMWB',

                      'IAB',

                      'LT5',

                      'GMDB',

                      'GMIB',

                      'HDV',

                      'HD',

                      'HDGRO')

            AND fc.transaction_count > 25

            AND (fc.contract_issue_date BETWEEN (SELECT REP_PERIOD_START_DT

                                                   FROM SAAS.FACT_COMPLIANCE

                                                  WHERE REP_PERIOD_START_DT IS NOT NULL

                                                        AND ROWNUM = 1)

                                            AND  (SELECT REP_PERIOD_end_DT

                                                    FROM SAAS.FACT_COMPLIANCE

                                                   WHERE REP_PERIOD_END_DT IS NOT NULL

                                                         AND ROWNUM = 1))

   GROUP BY fc.rep_period_start_dt,

            fc.rep_period_end_dt,

            fc.benefit_compliance_group,

            (CASE

                WHEN SUM ( (SUM (fc.total_amount))) = 0

                THEN

                   0

                ELSE

                   (SUM (fc.total_amount))

                   / SUM ( (SUM (fc.total_amount))) OVER ()

             END);

Open in new window

0
 

Author Comment

by:BadHatHarry
ID: 34112370
1) Can you elaborate on the changes/additions?
2) Any idea of why the newer Oracle version may cause this error?
0
 

Author Comment

by:BadHatHarry
ID: 34112390
1) I guess I know why you included the CASE in the GROUP BY clause, but I am at a loss to understand why the same query would work fine in 10.2.0.1, but not in 10.2.0.5
0
 
LVL 13

Accepted Solution

by:
dsmile earned 100 total points
ID: 34189727
Sorry for getting back lately :)

Since the cause of that specific error was this
A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

I just did what's right as instructed.

On a deeper search, I've found this more detailed explanation about this error code http://www.dba-oracle.com/sf_ora_00937_not_a_single_group_group_function.htm. (It states that you might face this error code on Windows environment)


If your case is that DEV environment and QA/UAT/Prod environment are only different in Oracle version and you're sure that the same query runs on 10.2.0.1 but not on 10.2.0.5 then I actually don't know exactly why either :)
There may be some patch from 10.2.0.1 to 10.2.0.5 that changed the behaviour of GROUP BY syntax.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Last record chosen in Oracle Query 3 54
I need to be able to return multiple values in my SQL case statement 12 93
Best RAID for a BDD Oracle 4 62
exp/imp 25 45
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now