Update Oracle SQL Query in a procedure but throwing missing right parenthesis error

The table sc contains the student id and the course id they attended, followed by it’s corresponding grade.

The table course contains the course id and corresponding credits

The table student1 contains the student id and corresponding info.

In this case I need to calculate the average score of every student and add the rank to table student1

The code is following:

create or replace procedure save_scholarship is
begin
    UPDATE STUDENT1
       SET STUDENT1.gpa = (
              select rank() over (order by sum(sc.grade*course.credit)/sum(course.credit) desc)
                from sc
                   , course 
               where sc.sno = student1.sno
                 and sc.cno = course.cno 
                 and sc.sno not in (select sc.sno from sc where grade < 60) 
            group by sc.sno
            order by sum(sc.grade*course.credit)/sum(course.credit) desc
           )
         ;

end save_scholarship;

This is the error I got:

SQL Error: ORA-00907: missing right parenthesis

The error is located on : order by sum(sc.grade*course.credit)/sum(course.credit) desc);

Anyone can help me? I am new to Oracle 11g currently studying it.

I tried to run the subquery of the update SQL but it performed as good as expected.

Just can’t figure it out.

Answer

Note: I’m pretty sure that the Oracle optimizer is not naive enough to regenerate the “ranked_students” subquery below for every row updated, but of course one should always test your optimizations on significant amounts of data where applicable, and use “explain plan” to decide what’s best.

• It appears to me that the issue with your query is that the rank() function is not producing the correct results, because it is only looking at a single student at a time, and therefore that student’s rank is always #1…with respect to themselves!

• I also think you probably want dense_rank() in case two students have equal GPAs.

So I made an example query that uses a “with” clause to rank all of the students (that don’t have a grade < 60), and then uses that to update the student1 table.

update student1
set student1.gpa = (
    with ranked_students as
    (
        select student1.sno,
            dense_rank() over (
                order by sum(sc.grade*course.credit)/sum(course.credit) desc
            ) as gpa
        from student1
        join sc on sc.sno = student1.sno
        join course on course.cno = sc.cno
        where not exists (
            select 1 from sc where sc.sno =student1.sno
            and sc.grade < 60
        )
        group by student1.sno
    )
    select gpa from ranked_students r
    where r.sno = student1.sno
);

select * from student1 order by gpa;

+-----+-------+-----+
| SNO | SNAME | GPA |
+-----+-------+-----+
| 002 | Alice | 1   |
| 001 | Bob   | 2   |
| 003 | Mary  |  -  |
+-----+-------+-----+

Here are statements to recreate my testing tables:

create table sc as
select '001' as sno, 'C1' as cno, 80 as grade from dual
union select '001' as sno, 'C3' as cno, 70 as grade from dual
union select '002' as sno, 'C2' as cno, 90 as grade from dual
union select '003' as sno, 'C2' as cno, 59 as grade from dual
union select '003' as sno, 'C5' as cno, 98 as grade from dual;

create table student1 as
select '001' as sno, 'Bob' as sname, 100 as gpa from dual
union select '002' as sno, 'Alice' as sname, 100 as gpa from dual
union select '003' as sno, 'Mary' as sname, 100 as gpa from dual;

create table course as
select 'C1' as cno, 4 as credit from dual
union select 'C2' as cno, 4 as credit from dual
union select 'C3' as cno, 3 as credit from dual
union select 'C4' as cno, 3 as credit from dual
union select 'C5' as cno, 1 as credit from dual;

The above tables describe Alice as having a high GPA, Bob as having a lower one, and Mary having a grade that is below 60 which disqualifies her from ranking.

Here is a query to illustrate the GPA averages:

select student1.sno, student1.sname,
   round(sum(sc.grade*course.credit)/sum(course.credit),0) as gpa
from student1
join sc on sc.sno = student1.sno
join course on course.cno = sc.cno
group by student1.sno, student1.sname
order by gpa desc;

+-----+-------+-----+
| SNO | SNAME | GPA |
+-----+-------+-----+
| 002 | Alice |  90 |
| 001 | Bob   |  76 |
| 003 | Mary  |  67 |
+-----+-------+-----+