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

    Unanswered: Errors with Program

    You guys have been great!! Learned a lot with the help you provided Can you tell me where I went wrong in this program? My output should display the city_total for DAYS input for each city.

    show error;

    CREATE OR REPLACE PROCEDURE CITY_REVENUE (Days IN number) IS

    CURSOR C1_PETS IS SELECT FOODNAME, DAILYFOODUNITS, OWNER_ID FROM PETS;

    C1_PETS_FOODNAME varchar2(20);
    C1_PETS_DAILYFOODUNITS varchar2(20);
    C1_PETS_OWNER_ID number (10);

    CURSOR C2_PETOWNER IS SELECT CITY, OWNER_ID FROM PET_OWNER;

    C2_PETOWNER_CITY varchar2(20);
    C2_PETOWNER_OWNER_ID number (10);


    CURSOR C3_PETFOOD IS SELECT FOODNAME, FOODUNITPRICE FROM PET_FOOD;

    C3_PETFOOD_FOODNAME varchar2(20);
    C3_PETFOOD_FOODUNITPRICE number (10);

    BEGIN


    CITY_TOTAL:= 0;

    OPEN C1_PETS;
    LOOP
    FETCH C1_PETS INTO C1_PETS_FOODNAME, C1_PETS_DAILYFOODUNITS, C1_PETS_OWNER_ID;
    EXIT WHEN C1_PETS%NOTFOUND;
    IF (C1_PETS_OWNER_ID = C2_PETOWNER_OWNER_ID) THEN


    OPEN C2_PETOWNER;
    LOOP
    FETCH C2_PETOWNER INTO C2_PETOWNER_CITY, C2_PETOWNER_OWNER_ID;
    EXIT WHEN C2_PETOWNER%NOTFOUND; IF
    CITY_TOTAL = (C1_PETS_DAILYFOODUNITS * C3_PETFOOD_FOUNDUNITPRICE * DAYS) THEN
    --GROUP BY CITY;

    OPEN C3_PETFOOD;
    LOOP
    FETCH C3_PETFOOD INTO C3_PETFOOD_FOODNAME, C3_PETFOOD_FOODUNITPRICE;
    EXIT WHEN C3_PETFOOD%NOTFOUND;
    IF (C3_PETFOOD_FOODUNITPRICE = C1_PETS_FOODUNITPRICE) THEN

    DBMS_OUTPUT.PUT_LINE ('TOTAL REVENUE FOR CITY IS '|| CITY_TOTAL);


    END IF;
    END LOOP;
    CLOSE C3_PETFOOD;

    END IF;
    END LOOP;
    --END LOOP;
    CLOSE C2_PETOWNER;

    END IF;
    END_LOOP;
    CLOSE C1_PETS;
    End;

  2. #2
    Join Date
    Sep 2003
    Posts
    8

    Re: Errors with Program

    Didn't got thru the logic as such, but you have one syntactic error.

    One of the End Loops is incorrectly spelled as END_LOOP. The underscore is not needed.

    - WiseDB


    Originally posted by terriep1
    You guys have been great!! Learned a lot with the help you provided Can you tell me where I went wrong in this program? My output should display the city_total for DAYS input for each city.

    show error;

    CREATE OR REPLACE PROCEDURE CITY_REVENUE (Days IN number) IS

    CURSOR C1_PETS IS SELECT FOODNAME, DAILYFOODUNITS, OWNER_ID FROM PETS;

    C1_PETS_FOODNAME varchar2(20);
    C1_PETS_DAILYFOODUNITS varchar2(20);
    C1_PETS_OWNER_ID number (10);

    CURSOR C2_PETOWNER IS SELECT CITY, OWNER_ID FROM PET_OWNER;

    C2_PETOWNER_CITY varchar2(20);
    C2_PETOWNER_OWNER_ID number (10);


    CURSOR C3_PETFOOD IS SELECT FOODNAME, FOODUNITPRICE FROM PET_FOOD;

    C3_PETFOOD_FOODNAME varchar2(20);
    C3_PETFOOD_FOODUNITPRICE number (10);

    BEGIN


    CITY_TOTAL:= 0;

    OPEN C1_PETS;
    LOOP
    FETCH C1_PETS INTO C1_PETS_FOODNAME, C1_PETS_DAILYFOODUNITS, C1_PETS_OWNER_ID;
    EXIT WHEN C1_PETS%NOTFOUND;
    IF (C1_PETS_OWNER_ID = C2_PETOWNER_OWNER_ID) THEN


    OPEN C2_PETOWNER;
    LOOP
    FETCH C2_PETOWNER INTO C2_PETOWNER_CITY, C2_PETOWNER_OWNER_ID;
    EXIT WHEN C2_PETOWNER%NOTFOUND; IF
    CITY_TOTAL = (C1_PETS_DAILYFOODUNITS * C3_PETFOOD_FOUNDUNITPRICE * DAYS) THEN
    --GROUP BY CITY;

    OPEN C3_PETFOOD;
    LOOP
    FETCH C3_PETFOOD INTO C3_PETFOOD_FOODNAME, C3_PETFOOD_FOODUNITPRICE;
    EXIT WHEN C3_PETFOOD%NOTFOUND;
    IF (C3_PETFOOD_FOODUNITPRICE = C1_PETS_FOODUNITPRICE) THEN

    DBMS_OUTPUT.PUT_LINE ('TOTAL REVENUE FOR CITY IS '|| CITY_TOTAL);


    END IF;
    END LOOP;
    CLOSE C3_PETFOOD;

    END IF;
    END LOOP;
    --END LOOP;
    CLOSE C2_PETOWNER;

    END IF;
    END_LOOP;
    CLOSE C1_PETS;
    End;

  3. #3
    Join Date
    Sep 2003
    Posts
    8

    Re: Errors with Program

    I think you have got the sequence all wrong as well.

    You coded the following if condition
    ** IF (C1_PETS_OWNER_ID = C2_PETOWNER_OWNER_ID) THEN
    But the C2_ variable is not even intialized.

    I guess it's the same issue in other places as well.

    You need to rework on the flow and vairable initialization.

    - WiseDB



    Originally posted by terriep1
    You guys have been great!! Learned a lot with the help you provided Can you tell me where I went wrong in this program? My output should display the city_total for DAYS input for each city.

    show error;

    CREATE OR REPLACE PROCEDURE CITY_REVENUE (Days IN number) IS

    CURSOR C1_PETS IS SELECT FOODNAME, DAILYFOODUNITS, OWNER_ID FROM PETS;

    C1_PETS_FOODNAME varchar2(20);
    C1_PETS_DAILYFOODUNITS varchar2(20);
    C1_PETS_OWNER_ID number (10);

    CURSOR C2_PETOWNER IS SELECT CITY, OWNER_ID FROM PET_OWNER;

    C2_PETOWNER_CITY varchar2(20);
    C2_PETOWNER_OWNER_ID number (10);


    CURSOR C3_PETFOOD IS SELECT FOODNAME, FOODUNITPRICE FROM PET_FOOD;

    C3_PETFOOD_FOODNAME varchar2(20);
    C3_PETFOOD_FOODUNITPRICE number (10);

    BEGIN


    CITY_TOTAL:= 0;

    OPEN C1_PETS;
    LOOP
    FETCH C1_PETS INTO C1_PETS_FOODNAME, C1_PETS_DAILYFOODUNITS, C1_PETS_OWNER_ID;
    EXIT WHEN C1_PETS%NOTFOUND;
    IF (C1_PETS_OWNER_ID = C2_PETOWNER_OWNER_ID) THEN


    OPEN C2_PETOWNER;
    LOOP
    FETCH C2_PETOWNER INTO C2_PETOWNER_CITY, C2_PETOWNER_OWNER_ID;
    EXIT WHEN C2_PETOWNER%NOTFOUND; IF
    CITY_TOTAL = (C1_PETS_DAILYFOODUNITS * C3_PETFOOD_FOUNDUNITPRICE * DAYS) THEN
    --GROUP BY CITY;

    OPEN C3_PETFOOD;
    LOOP
    FETCH C3_PETFOOD INTO C3_PETFOOD_FOODNAME, C3_PETFOOD_FOODUNITPRICE;
    EXIT WHEN C3_PETFOOD%NOTFOUND;
    IF (C3_PETFOOD_FOODUNITPRICE = C1_PETS_FOODUNITPRICE) THEN

    DBMS_OUTPUT.PUT_LINE ('TOTAL REVENUE FOR CITY IS '|| CITY_TOTAL);


    END IF;
    END LOOP;
    CLOSE C3_PETFOOD;

    END IF;
    END LOOP;
    --END LOOP;
    CLOSE C2_PETOWNER;

    END IF;
    END_LOOP;
    CLOSE C1_PETS;
    End;

  4. #4
    Join Date
    Sep 2003
    Posts
    9

    Post WHich way should I got next!!

    WiseDB,
    Thanks for your input. I want to use 3 explicit cusors to join the 3 tables. How would I go about joining the tables with explicit cusors. I don't know of anyway to implement without opening that cursor after the IF statment.

    terriep1

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

    I cannot figure out what this is all about.
    First of all the CITY_TOTAL is initialized to zero (0), futher on in the code you compare zero to a calculation of C1_PETS_DAILYFOODUNITS * C3_PETFOOD_FOUNDUNITPRICE * DAYS
    This will never work.

    I assume you have owners of pets that live in a city and consume petfood. The city is a master table and the PETS table is a child of PET_OWNER. Next, the PETFOOD table is master of the PETS table. Right ?
    In that case you want the following:

    From every DISTINCT CITY, count up all food unit prices all pets in that city consumed in the last X DAYS.

    Why do you want to do that in 3 explicit cursors ? Why not do so in only one cursor joing the three table together and adding everthing up in the cursor ? like this :

    select distinct(own.city)
    , sum(food.foodunitprice*pets.dailyfoodunits) citytotal
    from pet_food food
    , pets pets
    , pet_owner own
    where own.owner_id = pets.owner_id
    and pets.foodname = food.foodname
    group by own.city

    Then you only have to add the DAYS parameter to the query, but I don't in what table it is and you might be able to figure out your self.

    The code would be like:
    CREATE OR REPLACE PROCEDURE CITY_REVENUE (Days IN number) IS
    cursor c_city_totals is
    select distinct(own.city)
    , sum(food.foodunitprice*pets.dailyfoodunits) city_total
    from pet_food food
    , pets pets
    , pet_owner own
    where own.owner_id = pets.owner_id
    and pets.foodname = food.foodname
    group by own.city;
    CITY_TOTAL number := 0;
    CITY varchar2(30);
    BEGIN
    open c_city_totals;
    fetch c_city_totals into city ,city_total;
    while c_city_totals%FOUND
    loop
    DBMS_OUTPUT.PUT_LINE ('TOTAL REVENUE FOR CITY '||city ||' IS '|| CITY_TOTAL);
    fetch c_city_totals into city ,city_total;
    end loop;
    close c_city_totals;
    if city_total = 0
    then
    DBMS_OUTPUT.PUT_LINE ('No revenues found');
    end if;
    exception
    when others
    then
    DBMS_OUTPUT.PUT_LINE('When others exception');
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    end;
    /

    hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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