Oracle - SQL question

Unfortunately we cannot provide exact answers to homework questions.  We can only offer guidance.

That said:
I would look at eliminating the IN with the sub-select.

I would look at indexing.

Note: Functions like SUBSTR will cause Oracle to not be able to use any index on the column.

Also:
What are your tuning goals?  'faster' is not a goal.

Hi SlightWV,

Thx for replying,

If I am only looking for duplicate re-takes for certain courses how else, or where else should i place
the filter on COURSE ID?

Thx for pointing out that SUBSTR does not use the index on the field... I did not know that....

It seems that when I added the CASE, it slowed the SQL down....  I just thought that with EXISTS, it's better than COUNT, bec as soon as it finds a hit on the condition, it exits the SQL

Sorry, i do not know about tuning.... this is for work.... I am more of an end user.... the IT group is in another dept

tx! Sandra

>>It seems that when I added the CASE, it slowed the SQL down....

Probably not the case itself but the inner select that causes the slowdown.

>>this is for work....

It's just when we see COURSES/ACADEMICS and questions that start with "Show me a list of:", it sort of screams homework.

>>If I am only looking for duplicate re-takes for certain courses how else, or where else should i place

I'm thinking the data warehouse functions like ROW_NUMBER or RANK would help here.

I'm not fully following your requirements.

Can you provide a small test case with sample data and expected results?  That may help provide a picture better than you trying to explain it.

Hi SlightWV,

I removed:

a) the condition for filtering the CourseId
b) the ORDER BY at the end of SQL
c) the condition for testing the CASE_IFexistsFlag = 'Y'

Maybe this is really an issue of the tool:  PL DEVELOPER.... i am noticing that it returns rows in groups of 100

Are you familiar with this tool, is there a way to increase that to 5000 at a time

Below is revised SQL with filters and ORDER BY removed to capture any index processing

SELECT
           STUDENT_COURSEB.AC_ACADEMIC_YEAR
         , STUDENT_COURSEB.AC_ACADEMIC_PERIOD  
         , STUDENT_COURSEB.AC_COLLEGE
         , STUDENT_COURSEB.AC_STUDENT_CLASSIFICATION
         , STUDENT_COURSEB.AC_ID
         , STUDENT_COURSEB.AC_NAME
         , STUDENT_COURSEB.AC_PRIMARY_PROGRAM_IND
         , STUDENT_COURSEB.AC_Calc_MaxTerm
         
         , STUDENT_COURSEB.ID
         , STUDENT_COURSEB.COURSE_IDENTIFICATION
         , STUDENT_COURSEB.ACADEMIC_PERIOD
         , STUDENT_COURSEB.FINAL_GRADE        

         , STUDENT_COURSEB.Calc_IfExistsFlag

FROM
(
SELECT
            STUDENT_COURSEA.AC_ACADEMIC_YEAR
          , STUDENT_COURSEA.AC_ACADEMIC_PERIOD  
          , STUDENT_COURSEA.AC_COLLEGE
          , STUDENT_COURSEA.AC_STUDENT_CLASSIFICATION
          , STUDENT_COURSEA.AC_ID
          , STUDENT_COURSEA.AC_NAME
          , STUDENT_COURSEA.AC_PRIMARY_PROGRAM_IND
          , STUDENT_COURSEA.AC_Calc_MaxTerm
         
         , STUDENT_COURSEA.ID
         , STUDENT_COURSEA.COURSE_IDENTIFICATION
         , STUDENT_COURSEA.ACADEMIC_PERIOD
         , STUDENT_COURSEA.FINAL_GRADE        

         , CASE WHEN
                      EXISTS
                                  (
                                     SELECT  SC.ID
                                           , SC.ACADEMIC_YEAR
                                           , SC.course_IDentification
                                           
                                     FROM STUDENT_COURSE  SC
                                     
                                     WHERE SC.ID = STUDENT_COURSEA.ID
                                                  AND
                                           SC.COURSE_IDENTIFICATION = STUDENT_COURSEA.COURSE_IDENTIFICATION
                                                  AND
                                           SC.ACADEMIC_YEAR < '2012'
                                   )
                       THEN
                             'Y'
                       ELSE
                             'N'
           END
                                                     as Calc_IfExistsFlag    
 
FROM

(
SELECT
            ACADEMIC_STUDY.ACADEMIC_YEAR       AS AC_ACADEMIC_YEAR
          , ACADEMIC_STUDY.ACADEMIC_PERIOD     AS AC_ACADEMIC_PERIOD
          , ACADEMIC_STUDY.COLLEGE             AS AC_COLLEGE
          , ACADEMIC_STUDY.STUDENT_CLASSIFICATION  AS AC_STUDENT_CLASSIFICATION
          , ACADEMIC_STUDY.ID                  AS AC_ID
          , 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
         
         , STUDENT_COURSE.ID
         , STUDENT_COURSE.COURSE_IDENTIFICATION
         , STUDENT_COURSE.ACADEMIC_PERIOD
         , STUDENT_COURSE.FINAL_GRADE        
     
           
     
FROM
        ACADEMIC_STUDY             ACADEMIC_STUDY
        LEFT JOIN
                 STUDENT_COURSE    STUDENT_COURSE
                                    ON
                  (ACADEMIC_STUDY.ACADEMIC_YEAR = STUDENT_COURSE.ACADEMIC_YEAR
                                        AND                    
                              ACADEMIC_STUDY.ID = STUDENT_COURSE.ID)

WHERE

--      ACADEMIC_STUDY.ID = '200714348'
  --               AND
      ACADEMIC_STUDY.COLLEGE = 'MD'
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)              
     
                   
       
) STUDENT_COURSEA

) STUDENT_COURSEB

-- WHERE   STUDENT_COURSEB.Calc_IfExistsFlag = 'Y'

/*ORDER BY  STUDENT_COURSEB.AC_STUDENT_CLASSIFICATION
        , STUDENT_COURSEB.AC_NAME
        , STUDENT_COURSEB.COURSE_IDENTIFICATION
        , STUDENT_COURSEB.ACADEMIC_PERIOD*/

Hi SlightWV,

i found the place in PL developer where it controls number of rows:

Under
         Tools/Preferences/Sql Window/Records per page

Will go try it out...

tx!  sandra

>>Are you familiar with this tool,

Nope.  I'm a sqlplus command line type.

>>Below is revised SQL with filters

Not sure what I can do with the SQL other than make casual observations.  You still have an inner select with a case statement.

Without sample data and expected results along with your tuning goal, I really can't be of much more help.

Hi SlightWV,

Sample Data

STUDENT_COURSE  table
                                                 2011     Washington, George   OBGYN3301   C
                                                 2012    Washington, George   OBGYN3301   P

ACADEMIC_STUDY  table
                                              need this table bec of the STUDENT_CLASSIFICATION field
                                                tells me what year of medical school student is doing:  M1, M2, M3, M4

The above student is active in 2012, and retook OBGYN bec there is a record with the same Course_Id for a previous year

tx, sandra

oh, the above sample is testing one 2012 student courseid to see if any exist prior to 2012

     but if student took 10 classes for 2012 and there are 1500 medical students, then running the case SELECT
     at lease 15000 times..... maybe that what's slowing things down?

tx, sandra

I realize the data makes sense to you but I'm not following.

For example, I see the ACADEMIC_STUDY table has a column STUDENT_CLASSIFICATION and it has three values.  I don't have the joining data.

I don't know what the columns are for the course table.

Based on what you did provide, see if this example helps you any.

It returns any duplicate that has a repeating course_id based on year.

create table my_STUDENT_COURSE (year char(4), location varchar2(30), course_id varchar2(10), some_flag char(1)); 
insert into my_student_course values('2011','Washington, George','OBGYN3301','C');
insert into my_student_course values('2012','Washington, George','OBGYN3301','P');
commit;

select year, location, course_id, some_flag
from
(
select year, location, course_id, some_flag, row_number() over(partition by course_id order by year desc) rn
from my_student_course
)
where rn > 1
/

Select allOpen in new window

Hi SlightWV,

tx for your idea,

will go work on it now....

tx, sandra

Hi SlightWV,

i am not sure if RowNum is right for this situation
                .... as every row after row number 1 is assigned a row number > 1

did you mean the COUNT function?

i have to go to a meeting in a few minutes... will work on it more when return

tx so much, sandra

>>did you mean the COUNT function?

I don't think so but then again, I still don't understand your requirements.

>> .... as every row after row number 1 is assigned a row number > 1

It all depends on what columns you want to restart the numbering.  These columns are what you put in the 'partition by' clause.

I just created a simple test case based on the limited data you provided.

If you can provide a simple test case that shows what you are trying to do, we can provide working SQL.

The test case should show tables, raw data and expected results.

It doesn't have to be complete to ALL your tables.  Just enough to represent what you are trying to do.  You can then take our SQL and apply it to your situation.

thx... will get back to you....

HI SlightWV,

Just came back from meeting and am off on Friday...

So am closing this out as solved for now bec you gave me a good idea to work with

tx so much, Sandra

I would suggest you let me re-open this and we continue work on it when you get back next week.

An accepted answer that doesn't answer the question really doesn't help and you would likely need to pretty much ask a duplicate question when you get back to this and it would have to be deleted.

Hi SlightWV,

Hope you had a nice weekend....

i tried applying your idea... did not completely understand how to use it....

i modified the SQL not to use the CASE WHEN exists to a diff approach that might also be over complicated

It works with one Student id hard coded in the WHERE clause, yet runs too long when i comment it out and run it fully.

One reason i like nested SQL  or joined-tables made from virtual SQL is that i can test the little pieces of SQL individually

i think i made the SQL too complicated now... but in a way it shows the data that I need

Thx sooo much for helping me last week, I learned a new concept.
I spent the morning on this ... sometimes, i need to give an issue a rest by switching to something else to do,
and when i return with a fresh brain, an idea opens up, or i see something that i missed.

I realize that you do not have my data to test with, so thx for helping me given the work-limitations.

So I just wanted to show you what I did today... am not entirely comfortable with the ROW_NUM,  bec am able to remove duplicate rows using DISTINCT, unless ROW-NUM is faster.....


to recap:
Student enrolled in 2012, who in 2012 took a class bec it is a repeat of a class that already took in prev year.
The reason student is retaking class bec he got a poor grade.
for ex:  Student named:     Washington, George

first time he took OBGYN3301 he got a grade of C  (POOR GRADE)
                                                 2011     Washington, George   OBGYN3301   C

then he took the class again in 2012 and got a P  which is a better grade
                                                 2012    Washington, George   OBGYN3301   P




Students enrolled in 2012  (Academic Study table)
                         who took classes in 2012  (Student Course table with alias STUDENT_COURSEA)
                         who took the same classes prior to 2012  (Student Course talbe with alisas STUDENT_COURSEB)



the complicated SQL which runs long:  (Each nested select can be tested individually)

tx, sandra

SELECT DISTINCT
           AC_ACADEMIC_YEAR
         , AC_ID
         , AC_NAME
FROM

(
 SELECT

            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.NAME                AS AC_NAME
          , ACADEMIC_STUDY.PRIMARY_PROGRAM_IND AS AC_PRIMARY_PROGRAM_IND
         
-- FUNCTION FOR MAX TERM FOR A STUDENT          
          , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program) AS AC_Calc_MaxTerm,


 -- STUDENT_COURSEA
                 STUDENT_COURSEA.ACADEMIC_YEAR
                , STUDENT_COURSEA.ACADEMIC_PERIOD
                , STUDENT_COURSEA.ID
                , STUDENT_COURSEA.COURSE_IDENTIFICATION
                , STUDENT_COURSEA.FINAL_GRADE
 
 -- STUDENT_COURSEB
/*               , row_number() over(partition by STUDENT_COURSEB.ID
                                               ,STUDENT_COURSEB.COURSE_IDENTIFICATION
                                   order by STUDENT_COURSEB.ACADEMIC_YEAR DESC) as Calc_RN
*/                                  
                , STUDENT_COURSEB.ACADEMIC_PERIOD
                , STUDENT_COURSEB.ID
                , STUDENT_COURSEB.COURSE_IDENTIFICATION
                , STUDENT_COURSEB.FINAL_GRADE
                                     
 FROM
       ACADEMIC_STUDY ACADEMIC_STUDY,

-----VIRTUAL TABLE.... courses taken this year
      (
                 SELECT
                       STUDENT_COURSE.ID
                     , STUDENT_COURSE.ACADEMIC_YEAR
                     , STUDENT_COURSE.ACADEMIC_PERIOD
                     , STUDENT_COURSE.COURSE_IDENTIFICATION
                     , STUDENT_COURSE.FINAL_GRADE        

                 FROM STUDENT_COURSE
                 WHERE
--                         STUDENT_COURSE.ID = '201000683'   AND

                         STUDENT_COURSE.COLLEGE = 'MD'
                                  AND
                         STUDENT_COURSE.ACADEMIC_YEAR = '2012'
                                  AND
                              ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                               OR
                                STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')                                          
      ) STUDENT_COURSEA
   

-----VIRTUAL TABLE.... courses taken prev year


      ,(
       
           SELECT
                 STUDENT_COURSE.ID
               , STUDENT_COURSE.ACADEMIC_YEAR
               , STUDENT_COURSE.ACADEMIC_PERIOD
               , STUDENT_COURSE.COURSE_IDENTIFICATION
               , STUDENT_COURSE.FINAL_GRADE        

           FROM STUDENT_COURSE
           WHERE
--                 STUDENT_COURSE.ID = '201000683'                              AND          
                   STUDENT_COURSE.COLLEGE = 'MD'
                            AND
                   STUDENT_COURSE.ACADEMIC_YEAR < '2012'
                            AND
                        ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                         OR
                          STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')                                          
         ) STUDENT_COURSEB


WHERE

--    ACADEMIC_STUDY.ID = '201000683' AND
 
      ACADEMIC_STUDY.COLLEGE = 'MD'
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)              
     
--    LEFT JOIN:  ACADEMIC STUDY TO STUDENT_COURSEA      
                    AND
          (ACADEMIC_STUDY.ACADEMIC_YEAR = STUDENT_COURSEA.ACADEMIC_YEAR (+)
                                        AND                    
           ACADEMIC_STUDY.ID = STUDENT_COURSEA.ID (+))

--   EQUI JOIN:  STUDENT_COURSEA TO STUDENT_COURSEB  - IF THIS YEAR'S COURSE IS IN PREV YEAR
                            AND
          STUDENT_COURSEA.COURSE_IDENTIFICATION = STUDENT_COURSEB.COURSE_IDENTIFICATION
) STUDENT_COURSEC

>>I realize that you do not have my data to test with, so thx for helping me given the work-limitations.

I'm not expecting actual data.  I'm looking for a dummy table with sample data and expected results.

The dummy table and data should be close to your actual information so when I provide working SQL, you can take it and apply it to your tables.

I need a sample at this point because I don't understand your exact requirements and you continuing to explain it isn't working. If I had data, before and after, I can get a better idea of what you want.

Hi SlightWv,

thx... so i'll start simple

table: STUDENT_COURSE   contains all courses student took with the grade

fields:
         ACADEMIC_YEAR
         COLLEGE
         ACADEMIC_PERIOD
         ID
         NAME
         COURSE_IDENTIFICATION
         FINAL_GRADE

4 rows in Student Course:

2011    MD 201120  11111111 Washington, George   OBGY3301    C    <--- C  is a poor grade
2012    MD 201220  11111111 Washington, George    OBGN3301    P    <--- he retook it next year and got a P

2012    MD 201220 11111111 Washington, George    ANES3901      P  - got a good grade for this course on first try

2012    MD  201220 22222222 Lincoln, Abraham         OBGYN3301   P  - got a good grade for this course on first try


Both students took courses in 2012. Looking for a student who took a class this year that is a repeat bec he took it in a prev year.

Final Result set:

2012 Washington, George  

My approach today was to create 2 virtual tables:

STUDENT_COURSEA  - student course activity for year 2012
STUDENT_COURSEB - student course activty for year < 2012

EQUI join STUDENT_COURSEA   to STUDENT_COURSEB   ON  course_identification  (if match that means a student from 2012 took the course initially prior to 2012)

then do a select DISTINCT from the above equi join rows, just to return the student names. If the student repeated a few courses, that's ok, just want to list him once in final results set.

(I use another table called ACADEMIC STUDY, to get the field that tells me what year of medical school the student is in M1, M2, M3, M4 . The student has as many rows as the number of semesters he attended, so i just select the max term row,  but we can just work with the STUDENT COURSE data above if you like

however here is sample data for ACADEMIC STUDY

fields:
         ACADEMIC_YEAR
         COLLEGE
         ACADEMIC_PERIOD
         ID
         NAME
         COURSE_IDENTIFICATION
         STUDENT_CLASSIFICATION



2012  MD  201210 11111111 Washington, George  M4                  (201210    10=summer semester)
2012  MD  201220 11111111 Washington, George  M4                  (201220    20=fall           semester)
2012  MD  201230 11111111  Washington, George  M4                  (201230    30= spring    semester)


2012  MD  201210 22222222 Lincoln, Abraham  M4                  (201210    10=summer semester)
2012  MD  201220 22222222 Lincoln, Abraham  M4                  (201220    20=fall           semester)
2012  MD  201230 22222222 Lincoln, Abraham  M4                  (201230    30= spring    semester)

M4 is the student attribute field called STUDENT_CLASSIFICATION


Just want to pull these rows from ACADEMIC_STUDY:

2012  MD  201230 11111111  Washington, George  M4            (201230    30= spring    semester)
2012  MD  201230 22222222 Lincoln, Abraham        M4                  (201230    30= spring    semester)


Please note, that I am using tables that are denormalized from a data warehouse, THAT'S why you see the NAME field in the ACADEMIC_STUDY and STUDENT_COURSE tables.


 max(ACADEMIC_PERIOD)  for an ID within COLLEGE, Progrm using a function  BEC plain MAX(ACADEMIC_PERIOD)  was not enough... bec sometimes a student can be enrolled in 2 different colleges simultaneously.....yet for this example MAX() regular would work.

========================

So the final results set includes the STUDENT_CLASSIFICATION FIELD  for ex:  M4  from ACADEMIC_STUDY table

2012 Washington, George  M4  

==========

So I realize i forgot to include the ACADEMIC_STUDY.STUDENT_CLASSIFICATION the prev SQL, so fixed the SQL and am pasting it below

SELECT DISTINCT
           AC_ACADEMIC_YEAR
         , AC_STUDENT_CLASSIFICATION
         , AC_ID
         , AC_NAME
FROM

(
 SELECT

            ACADEMIC_STUDY.ACADEMIC_YEAR       AS AC_ACADEMIC_YEAR
          , ACADEMIC_STUDY.ACADEMIC_PERIOD     AS AC_ACADEMIC_PERIOD
          , ACADEMIC_STUDY.COLLEGE             AS AC_COLLEGE
          , ACADEMIC_STUDY.STUDENT_CLASSIFICATION AS AC_STUDENT_CLASSIFICATION
          , ACADEMIC_STUDY.ID                  AS AC_ID
          , ACADEMIC_STUDY.NAME                AS AC_NAME
          , ACADEMIC_STUDY.PRIMARY_PROGRAM_IND AS AC_PRIMARY_PROGRAM_IND
         
-- FUNCTION FOR MAX TERM FOR A STUDENT          
          , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program) AS AC_Calc_MaxTerm,


 -- STUDENT_COURSEA
                 STUDENT_COURSEA.ACADEMIC_YEAR
                , STUDENT_COURSEA.ACADEMIC_PERIOD
                , STUDENT_COURSEA.ID
                , STUDENT_COURSEA.COURSE_IDENTIFICATION
                , STUDENT_COURSEA.FINAL_GRADE
 
 -- STUDENT_COURSEB
/*               , row_number() over(partition by STUDENT_COURSEB.ID
                                               ,STUDENT_COURSEB.COURSE_IDENTIFICATION
                                   order by STUDENT_COURSEB.ACADEMIC_YEAR DESC) as Calc_RN
*/                                  
                , STUDENT_COURSEB.ACADEMIC_PERIOD
                , STUDENT_COURSEB.ID
                , STUDENT_COURSEB.COURSE_IDENTIFICATION
                , STUDENT_COURSEB.FINAL_GRADE
                                     
 FROM
       ACADEMIC_STUDY ACADEMIC_STUDY,
      (
                 SELECT
                       STUDENT_COURSE.ID
                     , STUDENT_COURSE.ACADEMIC_YEAR
                     , STUDENT_COURSE.ACADEMIC_PERIOD
                     , STUDENT_COURSE.COURSE_IDENTIFICATION
                     , STUDENT_COURSE.FINAL_GRADE        

                 FROM STUDENT_COURSE
                 WHERE
    ---                     STUDENT_COURSE.ID = '11111111'   AND

                         STUDENT_COURSE.COLLEGE = 'MD'
                                  AND
                         STUDENT_COURSE.ACADEMIC_YEAR = '2012'
                                  AND
                              ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                               OR
                                STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')                                          
      ) STUDENT_COURSEA
   
      ,(
       
           SELECT
                 STUDENT_COURSE.ID
               , STUDENT_COURSE.ACADEMIC_YEAR
               , STUDENT_COURSE.ACADEMIC_PERIOD
               , STUDENT_COURSE.COURSE_IDENTIFICATION
               , STUDENT_COURSE.FINAL_GRADE        

           FROM STUDENT_COURSE
           WHERE
          ---       STUDENT_COURSE.ID = '11111111'                              AND          
                   STUDENT_COURSE.COLLEGE = 'MD'
                            AND
                   STUDENT_COURSE.ACADEMIC_YEAR < '2012'
                            AND
                        ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                         OR
                          STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003')                                          
         ) STUDENT_COURSEB


WHERE

    --   ACADEMIC_STUDY.ID = '11111111' AND
 
      ACADEMIC_STUDY.COLLEGE = 'MD'
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)              
     
--    LEFT JOIN:  ACADEMIC STUDY TO STUDENT_COURSEA      
                    AND
          (ACADEMIC_STUDY.ACADEMIC_YEAR = STUDENT_COURSEA.ACADEMIC_YEAR (+)
                                        AND                    
           ACADEMIC_STUDY.ID = STUDENT_COURSEA.ID (+))

--   EQUI JOIN:  STUDENT_COURSEA TO STUDENT_COURSEB  - IF THIS YEAR'S COURSE IS IN PREV YEAR
                            AND
          STUDENT_COURSEA.COURSE_IDENTIFICATION = STUDENT_COURSEB.COURSE_IDENTIFICATION
) STUDENT_COURSEC


tx so much, sandra

Hi SlightWV,

tx sooooo much,

it works now.... faster, too!

For me to test and understand, as i mentioned earlier, i like to add layers of selects so i understand and can analyze the results set

ALSO, VERY IMPORTANT... i added this to the foundational WHERE:  testing for Academic_Year, bec am not interested in students who rn> 1, and for instance the "max" Student_Course record is 2006, I am interested if the student took the course in 2012, and then to see if anything exists prev    

where rn  > 1
AND STUDENT_COURSEA.ACADEMIC_YEAR  = '2012'

I joined the Academic Study table later on..... to get the Student_Classification field, bec i needed to display and sort the student names by the medical school year that they are in.....


Below is the working sql:  tx soooo much!!!!!!!

 SELECT
         
            ACADEMIC_STUDY.ACADEMIC_YEAR       AS AS_YEAR
          , ACADEMIC_STUDY.COLLEGE             AS AS_COLLEGE
          , ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program) AS AS_Calc_MaxTerm
          , ACADEMIC_STUDY.STUDENT_CLASSIFICATION AS AS_STUDENT_CLASSIFICATION

          , ACADEMIC_STUDY.ID                  AS AS_ID
          , ACADEMIC_STUDY.name                AS AS_NAME          
         

FROM
          ACADEMIC_STUDY   ACADEMIC_STUDY

, (SELECT DISTINCT STUDENT_COURSEB.ACADEMIC_YEAR
              , STUDENT_COURSEB.ID
              , STUDENT_COURSEB.name
FROM
(
select STUDENT_COURSEA.academic_year
     , STUDENT_COURSEA.COURSE_IDENTIFICATION
     , STUDENT_COURSEA.ID
     , STUDENT_COURSEA.name

     , STUDENT_COURSEA.FINAL_GRADE
     , STUDENT_COURSEA.RN

from (
select
            STUDENT_COURSE.academic_year
          , STUDENT_COURSE.ID
          , STUDENT_COURSE.name
          , STUDENT_COURSE.COURSE_IDENTIFICATION
          , STUDENT_COURSE.FINAL_GRADE
          , row_number() over(partition by STUDENT_COURSE.id
                                         , STUDENT_COURSE.course_identification
                              order by  STUDENT_COURSE.id
                                      , STUDENT_COURSE.course_identification
                                      , STUDENT_COURSE.academic_year ASC)
                                                                           AS rn
      from STUDENT_COURSE
where (STUDENT_COURSE.COLLEGE = 'MD'
                                  AND
                         STUDENT_COURSE.ACADEMIC_YEAR BETWEEN '2006' AND '2012'
                                  AND
                              ( SUBSTR(STUDENT_COURSE.COURSE_IDENTIFICATION,5,1) IN ('1', '2', '3')
                                               OR
                                STUDENT_COURSE.COURSE_IDENTIFICATION = 'MEDI4003'))

--ORDER BY STUDENT_COURSE.COURSE_IDENTIFICATION
  --            ,STUDENT_COURSE.NAME
    --          ,STUDENT_COURSE.ACADEMIC_YEAR desc
               
) STUDENT_COURSEA

where rn  > 1
AND STUDENT_COURSEA.ACADEMIC_YEAR  = '2012'

--ORDER BY STUDENT_COURSEA.COURSE_IDENTIFICATION
  --       ,STUDENT_COURSEA.NAME
         
) STUDENT_COURSEB

) STUDENT_COURSEC


WHERE

--      ACADEMIC_STUDY.ID = '200714348'
  --                 AND
 
      ACADEMIC_STUDY.COLLEGE = 'MD'
                    AND
      ACADEMIC_STUDY.ACADEMIC_YEAR = '2012'
                    AND
      ACADEMIC_STUDY.ACADEMIC_PERIOD =  ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_UID,'2012'||'30','N', ACADEMIC_STUDY.Program)              
     
-- JOINING:   STUDENT_COURSEC  TO ACADEMIC_STUDY
                    AND
          (STUDENT_COURSEC.ACADEMIC_YEAR = ACADEMIC_STUDY.ACADEMIC_YEAR (+)
                                        AND                    
           STUDENT_COURSEC.ID = ACADEMIC_STUDY.ID (+))





ORDER BY  
         ACADEMIC_STUDY.STUDENT_CLASSIFICATION
         , STUDENT_COURSEC.NAME

Glad to help!

:-)