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,'YYYY MM'),-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

On 21 Feb 2005 13:58:18 -0800, "McCormick" <mccormm (AT) indy (DOT) net> wrote:

That is a 7.3.4 client (desupported) with a 8.1.6 database
(desupported). At least database and client software are too far
apart.
Upgrade to something supported in the current century.


--
Sybrand Bakker, Senior Oracle DBA

McCormick wrote:
This piece of code reminds me of the example under "Coding Conventions"
in the beginning of Tom Kyte's excellent book "Expert One-on-One
Oracle".
Could it be that you ran into the same problem mentioned there?
That is, in the clause "ci.service_id = service_id", the second
"service_id" could be resolved to the same as the first, the column
in the table, and not to what you intended, the local variable
with the same name... Try renaming the local variable and tell us
what you get!

Martin W.

That worked great! I changed 'service_id' to simply 'service' and I'm
getting the numbers I'm expecting.

I'll keep an eye out for that book. Does it explain why the query
worked straight, but failed inside a Declaration?

Thanks again!

Mike McCormick