Query keeps returning 10434.41

From: McCormick <mccormm_at_indy.net>
Date: 21 Feb 2005 13:58:18 -0800
Message-ID: <1109023098.810902.189840@o13g2000cwo.googlegroups.com>

  Does anyone have any idea why, in SQL*Plus 3.3.4 under Oracle 8.1.6 (with all the patches), this query will work:

SELECT MAX(SUM(ci.quantity))
  INTO :v_ytd_peak
  FROM charge_item ci

 WHERE ci.service_id = 650
   AND ci.structure_element_type_id = 1
   AND ci.cycle <= TO_NUMBER('200501')
   AND ci.cycle >= TO_NUMBER(TO_CHAR(ADD_MONTHS(

TO_DATE('200501','YYYYMM') , -11),'YYYYMM'))  GROUP BY ci.cycle

MAX(SUM(CI.QUANTITY))

---

                240.6

But this function will not:

DECLARE
   service_id NUMBER := 650;
   c_cycle VARCHAR2(6) := '200501';
   v_ytd_peak NUMBER(10,3) := -5;
BEGIN
   DBMS_OUTPUT.PUT_LINE( v_ytd_peak );
   SELECT MAX(SUM(ci.quantity))

     INTO v_ytd_peak
     FROM charge_item ci
    WHERE ci.service_id = service_id
      AND ci.structure_element_type_id = 1
      AND ci.cycle <= TO_NUMBER(c_cycle)
      AND ci.cycle >=

TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(c_cycle,'YYYYMM'),-11),'YYYYM

    GROUP BY ci.cycle;
   DBMS_OUTPUT.PUT_LINE( v_ytd_peak );
EXCEPTION
   WHEN OTHERS THEN

                   DBMS_OUTPUT.PUT_LINE( 'Oops!' );

END;
SQL> /
-5
10434.41

Generally, the query gets a sum of records for a month over a 12 month period, grouped by month, then returns the Max of those 12 summations. The first, working query will produce a different, accurate answer for any value of system_id I put in. The second query always returns 10434.41.

Thanks!

Mike McCormick Received on Mon Feb 21 2005 - 15:58:18 CST