SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.INSTITUTION, A.STDNT_GROUP, B.STDNT_GROUP, D.NATIONAL_ID FROM PS_STDNT_GRPS_HIST A, PS_STDNT_GRPS_HIST B, PS_SCC_PERS_NI_QVW D, PS_CU_BIO_VW C WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_STDNT_GRPS_HIST A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.INSTITUTION = A_ED.INSTITUTION AND A.STDNT_GROUP = A_ED.STDNT_GROUP AND A_ED.EFFDT <= SYSDATE) AND substr ( A.STDNT_GROUP, 1, 2) IN ('M1','M2') AND A.EFF_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.STDNT_GROUP < B.STDNT_GROUP AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_STDNT_GRPS_HIST B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.INSTITUTION = B_ED.INSTITUTION AND B.STDNT_GROUP = B_ED.STDNT_GROUP AND B_ED.EFFDT <= SYSDATE) AND B.EFF_STATUS = 'A' AND substr ( B.STDNT_GROUP, 1, 2) IN ('M1','M2') AND A.INSTITUTION = 'BAR01' AND A.EMPLID = D.EMPLID AND A.EMPLID = C.EMPLID ORDER BY 1, 2, 3 | |||||||||||||||||||||