asked on

ORA-00937 error

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?

dsmile

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

BadHatHarry

ASKER

1) Can you elaborate on the changes/additions?
2) Any idea of why the newer Oracle version may cause this error?

BadHatHarry

ASKER

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

ASKER CERTIFIED SOLUTION

dsmile

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial