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

    Unanswered: Olympic medals chart script

    hai i having trouble in fix my codes:

    the compiler keep on indicating the (order by) part have error. i need help badly

    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 competitors.countryid, resulttime
    FROM competitors, entries, events
    WHERE competitors.competitorno = entries.competitorno
    AND entries.eventno = events.eventno
    AND events.eventcatno = v_event
    AND events.eventtype = 'F'
    AND entries.disqualified = 'N'
    ORDER BY events.eventcatno,resulttime)
    WHERE ROWNUM <= 3;

    tallies_cursor_row tallies_cursor%ROWTYPE;


    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;
    END;
    /


    ----------------------
    error message is :
    -----------------------
    ERROR at line 25:
    ORA-06550: line 25, column 4:
    PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
    . ( ) * @ % & - + / mod rem with an exponent (**) and or
    group having intersect minus start union where connect ||
    The symbol ")" was substituted for "ORDER" to continue.
    ORA-06550: line 25, column 41:
    PLS-00103: Encountered the symbol ")" when expecting one of the following:
    . ( , * @ % & - + ; / for mod rem an exponent (**) asc desc
    ||

  2. #2
    Join Date
    Oct 2003
    Posts
    17

    Order by not supported in Virtual Tables

    and hence the rownum is not resolved.


    select a.*
    from
    (select q2) a
    where rownum <= 3



    -A

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    what should i do to solve the prob. it give me an headache

  4. #4
    Join Date
    Oct 2003
    Posts
    2

    Re: Olympic medals chart script

    hi which oracle ur using
    if it is prior to 8 then u can't use
    ORDER BY clause in in-line query

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    if im using 8i, what should i do to do the calculation? i'm stuck in this part.... need help

  6. #6
    Join Date
    Oct 2003
    Posts
    17

    Try this.

    Try this...should work..

    Select a.*
    From
    (Select s1,s2,s3...
    From t1,t2,tn
    Group by s1,s2,s3... ) a
    where rownum <= 3

Posting Permissions

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