Page History

...

No Format

LISTAGG(CASE WHEN  M.STDNT_GROUP IN ('SEEKIEXP') THEN 'SEEKIEXP' 
WHEN  M.STDNT_GROUP IN ('PATHIESN') THEN 'PATHIESN' 
WHEN  M.STDNT_GROUP IN ('HPNSISS',) THEN 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORSISS' 
WHEN  M.STDNT_GROUP is null then 'Not Provided' 
ELSE NULL'OTHER' END, ', ') 

WITHIN GROUP (ORDER BY 
CASE WHEN  M.STDNT_GROUP IN ('SEEKIEXP') THEN 'SEEKIEXP' 
WHEN  M.STDNT_GROUP IN ('PATHIESN') THEN 'PATHIESN' 
WHEN  M.STDNT_GROUP IN ('HPNSISS',) THEN 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORSISS' 
WHEN  M.STDNT_GROUP is null then 'Not Provided' 
ELSE NULL'OTHER' 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 'SEEKIEXP', 'PATHIESN', 'HPNS', 'HREG', 'PROV', 'BSCH', 'DSCHISS'. Student may contain more than one value. Therefore, Listtagg resolve the occurrence of duplications in the report.

...

Query Output Image:

SQL Summery:

...

No Format

SELECT DISTINCT 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 (

   
 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 (

   
 decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') ||

decode (

   
 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

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

 
 END, LISTAGG(CASE

WHEN

 WHEN  M.STDNT_GROUP IN ('

SEEK

IEXP') THEN '

SEEK'
WHEN M.

IEXP' 
     WHEN  M.STDNT_GROUP IN ('

PATH

IESN') THEN '

PATH'
WHEN

IESN' 
     WHEN  M.STDNT_GROUP IN (

'HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END, ', ')
WITHIN GROUP (ORDER BY
CASE WHEN

'ISS') THEN 'ISS' 
 when  M.STDNT_GROUP is null then 'Not Provided' 
     ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY 
   CASE WHEN  M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' 
 WHEN  M.STDNT_GROUP IN ('

SEEK

IESN') THEN '

SEEK'
WHEN

IESN' 
     WHEN  M.STDNT_GROUP IN ('

PATH

ISS') THEN

'PATH'
WHEN

 'ISS' 
 when  M.STDNT_GROUP

IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL

 is null then 'Not Provided' 
 ELSE 'OTHER' END),G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')

FROM

 
   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

 
   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

 AND  C1.OPRCLASS = 'CUHCPPBAR01'

AND

 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 (

 M 
   WHERE ( A.EFFDT

=

 = 
         (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED

WHERE

 
         WHERE A.EMPLID = A_ED.EMPLID

AND

 
           AND A.ACAD_CAREER = A_ED.ACAD_CAREER

AND

 
           AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR

AND

 
           AND A_ED.EFFDT <= SYSDATE)

AND

 
     AND A.EFFSEQ

=

 = 
         (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES

WHERE

 
         WHERE A.EMPLID = A_ES.EMPLID

AND

 
           AND A.ACAD_CAREER = A_ES.ACAD_CAREER

AND

 
           AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR

AND

 
           AND A.EFFDT = A_ES.EFFDT)

AND

 
      AND A.EMPLID = B.EMPLID

AND

 
      AND A.ACAD_CAREER = B.ACAD_CAREER

AND

 
      AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR

AND

 
      AND A.EFFSEQ = B.EFFSEQ

AND

 
      AND A.EFFDT

= B.EFFDT
AND

 = B.EFFDT 
      AND A.INSTITUTION = :1

AND

 
      AND A.PROG_STATUS = 'AC'

AND

 
      AND A.EMPLID =

  C.EMPLID (+)

AND

 
      AND A.ACAD_CAREER =

  C.ACAD_CAREER (+)

AND

 
      AND A.STDNT_CAR_NBR =

  C.STDNT_CAR_NBR (+)

AND

 
      AND A.ADM_APPL_NBR =

  C.ADM_APPL_NBR (+)

AND

 
      AND A.INSTITUTION =

  C.INSTITUTION (+)

AND

 
      AND A.EMPLID = D.EMPLID

AND

 
      AND A.EMPLID = F.EMPLID

AND

 
      AND A.ACAD_CAREER = F.ACAD_CAREER

AND

 
      AND A.STDNT_CAR_NBR = F.STDNT_CAR_NBR

AND

 
      AND A.INSTITUTION = F.INSTITUTION

AND

 
      AND B.ACAD_PLAN = G.ACAD_PLAN

AND

 
      AND G.EFFDT

=

 = 
         (SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED

WHERE

 
         WHERE G.INSTITUTION = G_ED.INSTITUTION

AND

 
           AND G.ACAD_PLAN = G_ED.ACAD_PLAN

AND

 
           AND G_ED.EFFDT <= B.EFFDT)

AND

 
      AND A.INSTITUTION = G.INSTITUTION

AND

 
      AND A.ACAD_CAREER = :2

AND

 
      AND F.STRM = :3

AND

 
      AND F.UNT_TAKEN_PRGRSS >

0
AND (

 0 
      AND ( G.DEGREE = :4

OR

 
      OR :4 IS NULL)

AND (

 
      AND ( A.ACAD_PROG = :5

OR

 
      OR :5 IS NULL)

AND (

 
      AND ( B.ACAD_PLAN

= :6
     OR :6 IS NULL)
     AND A.EMPLID = E.EMPLID (+)
     AND 'APS' =

 = :6 
      OR :6 IS NULL) 
      AND A.EMPLID =  E.EMPLID (+) 
      AND 'APS' =  E.SRVC_IND_CD (+)

AND

 
      AND A.INSTITUTION =

  E.INSTITUTION (+)

AND

 
      AND A.EMPLID =

  H.EMPLID (+)

AND

 
      AND A.INSTITUTION =

  H.INSTITUTION (+)

AND

 
      AND 'ACP' =

  H.SRVC_IND_CD (+)

AND

 
      AND B.EMPLID =

  I.EMPLID (+

)
AND

) 
      AND B.ACAD_CAREER =

  I.ACAD_CAREER (+)

AND

 
      AND B.STDNT_CAR_NBR =

  I.STDNT_CAR_NBR (+)

AND

 
      AND B.EFFSEQ =

  I.EFFSEQ (+)

AND

 
      AND B.ACAD_PLAN =

  I.ACAD_PLAN (+)

AND

 
      AND B.EFFDT =

  I.EFFDT (+)

AND

 
      AND B.EMPLID =

  J.EMPLID (+)

AND

 
      AND B.ACAD_CAREER =

  J.ACAD_CAREER (+)

AND

 
      AND B.STDNT_CAR_NBR =

  J.STDNT_CAR_NBR (+)

AND

 
      AND B.EFFSEQ =

  J.EFFSEQ (+)

AND

 
      AND B.ACAD_PLAN

<>

 <>  J.ACAD_PLAN (+)

AND

 
      AND B.EFFDT =

  J.EFFDT (+)

AND

 
      AND J.EMPLID =

  K.EMPLID (+

)
AND

) 
      AND J.ACAD_CAREER =

  K.ACAD_CAREER (+)

AND

 
      AND J.STDNT_CAR_NBR =

  K.STDNT_CAR_NBR (+)

AND

 
      AND J.EFFSEQ =

  K.EFFSEQ (+)

AND

 
      AND J.ACAD_PLAN =

  K.ACAD_PLAN (+)

AND

 
      AND J.EFFDT =

  K.EFFDT (+)

AND

 
      AND B.PLAN_SEQUENCE = (SELECT MIN ( L.PLAN_SEQUENCE)

FROM

 
   FROM PS_ACAD_PLAN

L
WHERE (

 L 
   WHERE ( L.EMPLID = B.EMPLID

AND

 
      AND L.ACAD_CAREER = B.ACAD

_CAREER
AND

_CAREER 
      AND L.STDNT_CAR_NBR = B.STDNT_CAR_NBR

AND

 
      AND L.EFFDT = B.EFFDT

AND

 
      AND L.EFFSEQ = B.EFFSEQ ))

AND

 
      AND A.EMPLID =

  M.EMPLID (+)

AND

 
      AND A.INSTITUTION =

  M.INSTITUTION (+)

)
GROUP BY

 ) 
   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 (

   
 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 (

   
 decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') ||

decode (

   
 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

  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

 
   ORDER BY 2, 3, 4, 9

Page tree

Versions Compared

Old Version 1

New Version Current

Key

Query Output Image:

SQL Summery: