...
Records | Description of fields |
|---|---|
| STDNT_CAR_TERM | EMPLID, STERM, ACAD_CAREER |
| NAME_CURR_VW | LAST_NAME, FIRST_NAME |
| SCC_PERS_NI_QVW | NATIONAL_ID_TYPE, NATIONAL_ID |
| PERSNL_FERPA_VW | BIRTHDATE |
Subquery for A.STRM - Term | |
| STDNT_CAR_TERM | max (G.STRM) |
| UNION | |
| STDNT_CAR_TERM | EMPLID, ACAD_CAREER, STRM |
| NAME_CURR_VW | LAST_NAME, FIRST_NAME |
| EXTERNAL_SYSTEM | EXTERNAL_SYSTEM, EXTERNAL_SYSTEM_ID |
| PERSNL_FERPA_VW | BIRTHDATE |
Subquery for exists | |
| SCC_PERS_NI_QVW | EMPLID |
Subquery for E.STRM - Term | |
| STDNT_CAR_TERM | max (D.STRM) |
Legend of Query:
Expressions:
Few 'Expressions' have been added to find 'Maximum term' which pick up the latest update about students such as 'max (G.STRM)','max (D.STRM)' . 'SUBSTR(C.NATIONAL_ID,6,4)', 'SUBSTR(I.NATIONAL_ID,1,1)' have been used to mask national id.
Prompt:
Institution, From Enroll Term and To Enroll Term have been mandatory for an user to input.
Criteria:
...
Top Level of Query:
STERM is looking for maximum term through sub query and NAME_TYPE is primery 'PRI'.
UNION:
In the union it's looking for 'LID' through EXTERNAL_SYSTEM. However, NAME_TYPE is still looking for primary 'PRI' to avoid duplication. In the sub query it's looking for 'x' value in national_id if any exist. STERM is looking for maximum term through sub query as well.
Query Output Image:
Query SQL:
...