Solved

ORA-00937 error

Posted on 2010-11-10
4
501 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 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

6 Experts available now in Live!

Get 1:1 Help Now