Background:
Stephanie Govan already uses CU_BAR_SR_00045_1. She is requesting some additional data in the report. Based on the additional data, some of which is undergraduate or graduate specific, it might make sense to split out the query based on career.
Final Report Delivery with requirements:
...
Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'. Reference Reference for Service Indicator: Service Indicators
Prompt:
Institution, Career, Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.
Criteria:
| No Format |
|---|
decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') || decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')|| decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') || decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') || decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') || decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') || decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') || decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate') |
The decode function helps to determine the academic level for students based on their ACAD_LEVEL_BOT number.
| No Format |
|---|
CASE
WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END |
If the Total Transfer is grater than 0 then 'Y' else 'N'
| No Format |
|---|
LISTAGG(CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END, ', ')
WITHIN GROUP (ORDER BY
CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END) |
Listtagg function helps to fetch multiple values in a single row. it's an aggregate function. In this example, the report determine student based on their student group with multiple value such as 'SEEK', 'PATH', 'HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH'. Student may contain more than one value. Therefore, Listtagg resolve the occurrence of duplications in the report.
Prompt
Institution, Career, Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.
Criteria:
ACAD_PROG_STATUS is 'AC' ACAD_PROG_STATUS is 'AC' because the report is based on active students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code through SERV_IND_DATA record. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. . Added STNDT_GRPS to get student group information.
Query Output Image:
SQL Summery:
...