McCormick wrote:
>
> 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;
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.
Received on Tue Feb 22 2005 - 04:05:25 CST
