Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-00937 error

Posted on 2010-11-10
4
Medium Priority
?
524 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 300 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

670 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