Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: olympic "how to get it shorter

    i'm having problem in shortening the "counter" part. please give some advice.....

    DECLARE

    CURSOR event_cursor IS
    SELECT eventcatno FROM EVENTCATEGORIES;

    v_event EVENTCATEGORIES.eventcatno%TYPE;


    CURSOR countid_cursor IS
    SELECT countryid FROM COUNTRIES;

    v_countryid COUNTRIES.countryid%TYPE;
    v_tallcountid TALLIES.countryid%TYPE;
    v_counter NUMBER(1) := 0;


    CURSOR tallies_cursor IS
    SELECT * FROM (SELECT countryid, resulttime
    FROM competitors NATURAL JOIN entries NATURAL JOIN events NATURAL JOIN countries
    WHERE events.eventcatno = v_event
    AND events.eventtype = 'F'
    AND entries.disqualified = 'N'
    ORDER BY resulttime)
    WHERE ROWNUM <= 3;

    tallies_cursor_row tallies_cursor%ROWTYPE;

    error_message VARCHAR2(512);
    no_data_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(no_data_found, -0001);


    BEGIN
    DELETE FROM tallies;
    OPEN event_cursor;
    OPEN countid_cursor;
    FETCH event_cursor INTO v_event;

    WHILE event_cursor%FOUND LOOP
    FOR tallies_cursor_row IN tallies_cursor LOOP

    FETCH countid_cursor INTO v_tallcountid;

    WHILE countid_cursor%FOUND LOOP
    INSERT INTO tallies VALUES(v_tallcountid,0,0,0);
    FETCH countid_cursor INTO v_tallcountid;
    END LOOP;

    v_counter :=v_counter + 1;

    IF v_counter = 1 THEN
    UPDATE tallies
    SET gold = gold + 1
    WHERE countryid = tallies_cursor_row.countryid;

    ELSIF v_counter = 2 THEN
    UPDATE tallies
    SET silver = silver + 1
    WHERE countryid = tallies_cursor_row.countryid;

    ELSE
    UPDATE tallies
    SET bronze = bronze + 1
    WHERE countryid = tallies_cursor_row.countryid;

    v_counter :=0;

    END IF;
    END LOOP;
    FETCH event_cursor INTO v_event;
    END LOOP;

    CLOSE event_cursor;
    CLOSE countid_cursor;
    COMMIT;
    EXCEPTION
    WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('!!! There no record found !!!');
    WHEN OTHERS THEN
    error_message := SQLERRM;
    DBMS_OUTPUT.PUT_LINE('This program encountered following error:');
    DBMS_OUTPUT.PUT_LINE(error_message);
    END;
    /

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you use the row_number() analytic function you can assign a medal column to each competitor/event and then select the top 3. Then use a group by and sum function to calculate your medal tally for all countries in one sql statement.

    i.e.


    select countryid,
    sum(case when medal=1 then 1 else null end) golds,
    sum(case when medal=2 then 1 else null end) silver,
    sum(case when medal=3 then 1 else null end) bronze
    from
    (
    select countryid, competitor,
    row_number() over (PARTITION BY event ORDER BY resulttime asc) medal
    from
    (
    -- your sql here
    )
    )
    where medal <= 3 -- i.e. pick out first 3 competitors
    group by countryid


    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    sir i'm still confuse with the code....

    is it something like this:

    select countryid,
    sum(case when medal=1 then 1 else null end) gold,
    sum(case when medal=2 then 1 else null end) silver,
    sum(case when medal=3 then 1 else null end) bronze
    from
    (
    select countryid, competitor,
    row_number() over (PARTITION BY events ORDER BY resulttime asc) medal
    from
    (
    SELECT countryid, resulttime
    FROM competitors NATURAL JOIN entries NATURAL JOIN
    events NATURAL JOIN countries
    WHERE
    eventtype = 'F'
    AND disqualified = 'N'))
    where medal <= 3 -- i.e. pick out first 3 competitors
    group by countryid;


    (there were error with the "PARTITION BY events " part what should i do)

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your query has to provide all the columns referenced in the outer queries.

    So you need to provide some sort of unique event identifier to the outer query (eventcatno?) and then stick that in the partition by clause.

    Look up Analytic functions in the SQL manual (came in with 8i) for more info .

    select countryid,
    sum(case when medal=1 then 1 else null end) golds,
    sum(case when medal=2 then 1 else null end) silver,
    sum(case when medal=3 then 1 else null end) bronze
    from
    (
    select countryid, eventcatno,
    row_number() over (PARTITION BY eventcatno ORDER BY resulttime asc) medal
    from
    (
    SELECT countryid, resulttime, eventcatno
    FROM competitors NATURAL JOIN entries NATURAL JOIN
    events NATURAL JOIN countries
    WHERE
    eventtype = 'F'
    AND disqualified = 'N'
    )
    )
    where medal <= 3 -- i.e. pick out first 3 competitors
    group by countryid

    Alan

Posting Permissions

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