Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Exclamation Unanswered: Error in Program (THe Sum Function)

    I'm getting an error with the sum function. Can anyone see what went wrong?

    Thanks--

    show errors;

    CREATE OR REPLACE PROCEDURE CITY_REVENUE (DAYS IN NUMBER) IS

    CURSOR CITY_TOTAL IS
    SELECT FOODNAME, DAILYFOODUNITS, CITY, FOODUNITPRICE
    FROM PETS, PET_OWNER, PET_FOOD
    WHERE PETS.OWNER_ID = PET_OWNER.OWNER_ID AND
    PETS.FOODNAME = PET_FOOD.FOODNAME
    GROUP BY CITY;

    CITY_TOTAL_FOODNAME VARCHAR2(20);
    CITY_TOTAL_DAILYFOODUNITS NUMBER (10);
    CITY_TOTAL_CITY VARCHAR2(20);
    CITY_TOTAL_FOODUNITPRICE NUMBER (10);
    CITY_TOTAL_OWNER_ID NUMBER (10);

    BEGIN

    OPEN CITY_TOTAL;
    LOOP
    FETCH CITY_TOTAL INTO CITY_TOTAL_FOODNAME, CITY_TOTAL_DAILYFOODUNITS, CITY_TOTAL_CITY, CITY_TOTAL_FOODUNITPRICE, CITY_TOTAL_OWNER_ID;
    EXIT WHEN CITY_TOTAL%NOTFOUND;
    SUM := (CITY_TOTAL_DAILYFOODUNITS * CITY_TOTAL_FOODUNITPRICE * DAYS);
    DBMS_OUTPUT.PUT_LINE ('TOTAL REVENUE FOR CITY:' || CITY_TOTAL_CITY || 'IS' || SUM);

    END IF;
    END LOOP;
    CLOSE CITY_TOTAL;
    END CITY_REVENUE;

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Hi,

    why not use the sum function in your cursor ?
    SUM is not a valid pl/sql function, but only a SQL function.
    So, if you want to add up within a loop try :

    lv_total := lv_total + city_total_foodno;

    Good luck.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    Two problems I see: the fetch statement is asking for 5 values but the cursor is selecting only 4, also since SUM is a reserved word Oracle may complain about the use of that as a variable name.

    I'm not sure exactly what you are trying to accomplish but it might be simpler to use a single select statement to perform the sum and not process each row yourself - let Oracle do the work.

    Replace the cursor's select with something like this:
    select city, sum(dailyfoodunits*foodunitprice) pricesum
    from ...
    where ...
    group by city

    Then in the loop, fetch only city and pricesum and multiply the pricesum by the DAYS variable for display.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •