Solved

ORA-00937 error

Posted on 2010-11-10
4
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

749 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