My user is asking the following question:
Show me a list of:
a) Medical Students
b) enrolled in 2012
c) who took a Required course in 2012, that already took in a previous year (before 2012)
in other words, who repeated a course
I am coding/running the SQL in PL/Developer.
To make it easier to test, I coded it as nested SELECTS, this way i can Highlight each piece and run it in a separate
It's taking too long to run.
Notes: 2 tables:
ACADEMIC_STUDY - shows the semesters that students attended
i am pulling the latest semester the student attended so that it distincts to one
row per student MAX(ACADEMIC_PERIOD) using a function
STUDENT_COURSE - shows the semesters and courses for semester that student took
Notes: The Semester field is ACADEMIC_PERIOD
We have three semesters for ACADEMIC_YEAR
Student's latest semester could be 201230 if active student
Student's latest semester could be 201220 if took a leave of absence
If Student's latest semester is 201230, then for sure he has 201210.201220 record in ACADEMIC STUDY
but we only want one row for a student to keep things neat, which is why we select the MAX(ACADEMIC_PERIOD)
I am not a SQL expert.
Any help you can provide, if you feel a different approach would be better etc.
would be very appreciated.
tx! Sandra Below is the SQL
, STUDENT_COURSEA. AC_STUDENT_CLASSIFICATION
, CASE WHEN
\ , SC.ACADEMIC_YEAR
FROM STUDENT_COURSE SC
WHERE SC.ID = STUDENT_COURSEA.ID
SC.COURSE_IDENTIFICATION = STUDENT_COURSEA.COURSE_IDENTIFICATION
SC.ACADEMIC_YEAR < '2012'
ACADEMIC_STUDY.ACADEMIC_YEAR AS AC_ACADEMIC_YEAR
, ACADEMIC_STUDY.ACADEMIC_PERIOD AS AC_ACADEMIC_PERIOD
, ACADEMIC_STUDY.COLLEGE AS AC_COLLEGE
, ACADEMIC_STUDY.ID AS AC_ID
, ACADEMIC_STUDY.STUDENT_CLASSIFICATION AS AC_STUDENT_CLASSIFICATION
, ACADEMIC_STUDY.NAME AS AC_NAME
, ACADEMIC_STUDY.PRIMARY_PROGRAM_IND AS AC_PRIMARY_PROGRAM_IND
, ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program) AS AC_Calc_MaxTerm
(ACADEMIC_STUDY.ACADEMIC_YEAR = STUDENT_COURSE.ACADEMIC_YEAR
ACADEMIC_STUDY.ID = STUDENT_COURSE.ID)
ACADEMIC_STUDY.COLLEGE = 'MD'
ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
ACADEMIC_STUDY.ACADEMIC_PERIOD = ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM (ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)
( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')