You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

 

 

 

 

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:

CU_BAR_SR_00045_3 Prompts are below:  

  • Institution: Bar01 
  • Career: UGRD 
  • Term: pick any term 
  • Degree: (optional) ( pick any degree) 
  • AcadProg (optional) If you want to be more specific
  • AcadPlan (optional) If you want to be more specific 
     

Query Development:

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

Records
Description of fields
ACAD_PROGEMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG,
ACAD_PLANACAD_PLAN,PLAN_SEQ,
ADM_APPL_DATAADMIT_TYPE
CU_BOI_VWLAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR
STDNT_CAR_TERM ACADEMIC LEVEL,UNT_TAKEN_PRGRSS,TOT_CUM,TOT_PASSED_GPA, CUM_GPA
ACAD_PLAN_TBLDESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)
SERV_IND_DATASRVC_IND_CD for 'APS' have used it with reason code
SERV_IND_DATASRVC_IND_CD for 'ACP' have used it with reason code
ACAD_SUBPLANDESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)
ACAD_PLANACAD_PLAN
ACAD_SUBPLAN

ACAD_SUB_PLAN

STNDT_GRPSStudent Group (SG)

Expressions:

Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'.  Reference for Service Indicator: Service Indicators

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.

CASE 
WHEN  F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N' 
END

If the Total Transfer is grater than 0 then 'Y' else 'N'

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' 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:

 
SELECT A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.PLAN_SEQUENCE, B.ACAD_PLAN, I.ACAD_SUB_PLAN, J.ACAD_PLAN, K.ACAD_SUB_PLAN, G.DESCR, G.DEGREE, A.DEGR_CHKOUT_STAT, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') ||
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'), F.UNT_TAKEN_PRGRSS, F.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, D.PHONE, D.EMAIL_ADDR,  E.SRVC_IND_CD || ' ' ||  E.SRVC_IND_REASON,  H.SRVC_IND_CD || ' ' ||  H.SRVC_IND_REASON, F.TOT_TRNSFR, CASE
WHEN  F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END, 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),G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
  FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.STDNT_CAR_NBR, TC.ADM_APPL_NBR, TC.INSTITUTION
  FROM PS_ADM_APPL_DATA TC,PS_ADM_APPL_SCTY C1 WHERE TC.EMPLID = C1.EMPLID AND TC.ACAD_CAREER = C1.ACAD_CAREER AND TC.ADM_APPL_NBR = C1.ADM_APPL_NBR AND  C1.OPRCLASS = 'CUHCPPBAR01' AND  C1.OPRID = '11002650' ) C, PS_CU_BIO_VW D, PS_STDNT_CAR_TERM F, PS_ACAD_PLAN_TBL G, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA H, PS_ACAD_SUBPLAN I, PS_ACAD_PLAN J, PS_ACAD_SUBPLAN K, PS_STDNT_GRPS M
  WHERE ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ACAD_CAREER = A_ED.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
          AND A_ED.EFFDT <= SYSDATE)
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.ACAD_CAREER = A_ES.ACAD_CAREER
          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
          AND A.EFFDT = A_ES.EFFDT)
     AND A.EMPLID = B.EMPLID
     AND A.ACAD_CAREER = B.ACAD_CAREER
     AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND A.EFFSEQ = B.EFFSEQ
     AND A.EFFDT = B.EFFDT
     AND A.INSTITUTION = :1
     AND A.PROG_STATUS = 'AC'
     AND A.EMPLID =  C.EMPLID (+)
     AND A.ACAD_CAREER =  C.ACAD_CAREER (+)
     AND A.STDNT_CAR_NBR =  C.STDNT_CAR_NBR (+)
     AND A.ADM_APPL_NBR =  C.ADM_APPL_NBR (+)
     AND A.INSTITUTION =  C.INSTITUTION (+)
     AND A.EMPLID = D.EMPLID
     AND A.EMPLID = F.EMPLID
     AND A.ACAD_CAREER = F.ACAD_CAREER
     AND A.STDNT_CAR_NBR = F.STDNT_CAR_NBR
     AND A.INSTITUTION = F.INSTITUTION
     AND B.ACAD_PLAN = G.ACAD_PLAN
     AND G.EFFDT =
        (SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED
        WHERE G.INSTITUTION = G_ED.INSTITUTION
          AND G.ACAD_PLAN = G_ED.ACAD_PLAN
          AND G_ED.EFFDT <= B.EFFDT)
     AND A.INSTITUTION = G.INSTITUTION
     AND A.ACAD_CAREER = :2
     AND F.STRM = :3
     AND F.UNT_TAKEN_PRGRSS > 0
     AND ( G.DEGREE = :4
     OR :4 IS NULL)
     AND ( A.ACAD_PROG = :5
     OR :5 IS NULL)
     AND ( B.ACAD_PLAN = :6
     OR :6 IS NULL)
     AND A.EMPLID =  E.EMPLID (+)
     AND 'APS' =  E.SRVC_IND_CD (+)
     AND A.INSTITUTION =  E.INSTITUTION (+)
     AND A.EMPLID =  H.EMPLID (+)
     AND A.INSTITUTION =  H.INSTITUTION (+)
     AND 'ACP' =  H.SRVC_IND_CD (+)
     AND B.EMPLID =  I.EMPLID (+)
     AND B.ACAD_CAREER =  I.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR =  I.STDNT_CAR_NBR (+)
     AND B.EFFSEQ =  I.EFFSEQ (+)
     AND B.ACAD_PLAN =  I.ACAD_PLAN (+)
     AND B.EFFDT =  I.EFFDT (+)
     AND B.EMPLID =  J.EMPLID (+)
     AND B.ACAD_CAREER =  J.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR =  J.STDNT_CAR_NBR (+)
     AND B.EFFSEQ =  J.EFFSEQ (+)
     AND B.ACAD_PLAN <>  J.ACAD_PLAN (+)
     AND B.EFFDT =  J.EFFDT (+)
     AND J.EMPLID =  K.EMPLID (+)
     AND J.ACAD_CAREER =  K.ACAD_CAREER (+)
     AND J.STDNT_CAR_NBR =  K.STDNT_CAR_NBR (+)
     AND J.EFFSEQ =  K.EFFSEQ (+)
     AND J.ACAD_PLAN =  K.ACAD_PLAN (+)
     AND J.EFFDT =  K.EFFDT (+)
     AND B.PLAN_SEQUENCE = (SELECT MIN ( L.PLAN_SEQUENCE)
  FROM PS_ACAD_PLAN L
  WHERE ( L.EMPLID = B.EMPLID
     AND L.ACAD_CAREER = B.ACAD_CAREER
     AND L.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND L.EFFDT = B.EFFDT
     AND L.EFFSEQ = B.EFFSEQ ))
     AND A.EMPLID =  M.EMPLID (+)
     AND A.INSTITUTION =  M.INSTITUTION (+) )
  GROUP BY  A.EMPLID,  D.LAST_NAME,  D.FIRST_NAME,  D.MIDDLE_NAME,  C.ADMIT_TYPE,  A.ADMIT_TERM,  A.ACAD_CAREER,  A.ACAD_PROG,  B.PLAN_SEQUENCE,  B.ACAD_PLAN,  I.ACAD_SUB_PLAN,  J.ACAD_PLAN,  K.ACAD_SUB_PLAN,  G.DESCR,  G.DEGREE,  A.DEGR_CHKOUT_STAT,  G.SSR_NSC_CRD_LVL,  decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') ||
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'),  F.UNT_TAKEN_PRGRSS,  F.TOT_CUMULATIVE,  F.TOT_PASSD_GPA,  F.CUM_GPA,  D.PHONE,  D.EMAIL_ADDR,   E.SRVC_IND_CD || ' ' ||  E.SRVC_IND_REASON,   H.SRVC_IND_CD || ' ' ||  H.SRVC_IND_REASON,  F.TOT_TRNSFR,  CASE
WHEN  F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
  ORDER BY 2, 3, 4, 9
  • No labels