this is my pl/sql code for selecting students enrolled in a summer course.. i need to order them by the section id
attached is the sql file to create tables needed.. if u want to test it.. the result will be like this
and an image of the result in case..
all i need is to know how to show the section ids in a descending order..
here is the code
Code:
set serveroutput on size 4000
declare
callid course.call_id%type;
secnum course_section.sec_num%type;
secid course_section.c_sec_id%type;
fname student.s_first%type;
lname student.s_last%type;
cursor c1 is
select course.call_id,sec_num,c_sec_id
from course join course_section
on course.course_id=course_section.course_id
join term on course_section.term_id=term.term_id where term.term_id=(select term_id from term where term_desc='Summer 2007');
cursor c2(sec number) is
select s_first,s_last from student join enrollment on student.s_id=enrollment.s_id where enrollment.c_sec_id=sec;
begin
open c1;
loop
fetch c1 into callid,secnum,secid;
exit when c1%notfound;
dbms_output.put_line('=====================');
dbms_output.put_line(callid||' Sec. '||secnum);
dbms_output.put_line('=====================');
open c2(secid);
loop
fetch c2 into fname,lname;
exit when c2%notfound;
dbms_output.put_line(fname||' '||lname);
end loop;
close c2;
end loop;
close c1;
end;
cursor c1 is
select course.call_id,sec_num,c_sec_id
from course join course_section
on course.course_id=course_section.course_id
join term on course_section.term_id=term.term_id where term.term_id=(select term_id from term where term_desc='Summer 2007')
order by c_sec_id;
ORA-06550: line 13, column 1:
PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor
You do not need the subquery to get the termid you can use this
cursor c1 is
select course.call_id,sec_num,c_sec_id
from course join course_section
on course.course_id=course_section.course_id
join term on course_section.term_id=term.term_id
where term.term_desc='Summer 2007'
order by c_sec_id;