Advertisement
ma39isy

Untitled

Dec 10th, 2023
826
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT DISTINCT S.STUDENTID, S.FULLNAME, S.CLASS, S.CLASS2, S.CLASS3, S.LECTURERCODE, S.ACTIVESTATUS, S.STUDYPROGRAMID, F.GPA,
  2.                     S.STUDENTSCHOOLYEAR, S.STUDYFIELD, S.OUTDATE, S.PARTICIPANTNUMBER, S.NOTES, S.STUDENTTYPEID,
  3.                     S.STARTDATE, S.CLASSIFICATIONID,ST.STUDENTTYPENAME,CASE WHEN UPPER( C.CLASSIFICATIONNAME ) LIKE '%INTER%CLASS%' THEN C.CLASSIFICATIONNAME ELSE SP.STUDYPROGRAMNAME END AS STUDYPROGRAMNAME,ADDRESS,
  4.                     POSTALCODE,
  5.                     PROVINCENAME, T.PHONE,
  6.                     (SELECT "VALUE" FROM MASTERDATA."ACCOUNT" WHERE STUDENTID=S.STUDENTID AND INFOID IN (7,8,26) AND "VALUE" IS NOT NULL AND rownum<=1) EMAIL,
  7.                     "email_unitel" EMAILINSTITUSI,
  8.                     TAK, GENDER, DESCRIPTIONOFSELECTIONPATH, CASE WHEN UPPER(C.CLASSIFICATIONNAME) LIKE '%INTER%CLASS%' OR S.STUDYPROGRAMID=40 OR UPPER(SP.STUDYPROGRAMNAME) LIKE '%INTER%CLASS%' THEN 'KELAS INT'
  9.                     WHEN UPPER(C.CLASSIFICATIONNAME) LIKE '%PINDAHAN%' OR UPPER(SP.STUDYPROGRAMNAME) LIKE '%PINDAHAN%' THEN 'PINDAHAN'
  10.                     ELSE 'REGULER' END AS PROGRAMTYPE, AD.NATIONALITY, TO_CHAR (
  11.                         AD.BIRTHDATE,
  12.                         'DD-MM-YYYY'
  13.                     ) BIRTHDATE, YUDISIUMSTATUS, ETHNIC
  14.                     FROM MASTERDATA.STUDENT S
  15.                     left join MASTERDATA.ADDITIONALSTUDENTDATA AD ON (S.STUDENTID=AD.STUDENTID)
  16.                     left join MASTERDATA.STUDENTTYPE ST ON (S.STUDENTTYPEID=ST.STUDENTTYPEID)
  17.                     left join MASTERDATA.STUDYPROGRAM SP ON (S.STUDYPROGRAMID=SP.STUDYPROGRAMID)
  18.                     left join MASTERDATA.STUDENTSFAMILY SF ON (S.STUDENTID=SF.STUDENTID AND RELATIONSHIP IN('FATHER','ORANG TUA','AYAH') AND ADDRESS IS NOT NULL)
  19.                     left join MASTERDATA.PROVINCES P ON (SF.PROVINCE=TO_CHAR(P.PROVINCEID))
  20.                     LEFT JOIN GPASTUDENT F ON(F.STUDENTID=S.STUDENTID)
  21.                     LEFT JOIN (SELECT MAX("VALUE") PHONE, STUDENTID
  22.                        FROM MASTERDATA."ACCOUNT"
  23.                       WHERE INFOID = 22 GROUP BY STUDENTID) T
  24.                     ON (T.STUDENTID=S.STUDENTID)
  25.                     LEFT JOIN MASTERDATA.STUDENT_TOKEN ON (S.STUDENTID="nim")
  26.                     LEFT JOIN (SELECT STUDENTID, SUM(MHS_POINT) TAK FROM TAK.VIEW_STUDENT_TAK GROUP BY STUDENTID) TA ON (S.STUDENTID=TA.STUDENTID)
  27.                     LEFT JOIN ADMISSION.SELECTIONPATHS ON (SELECTIONPATH=SELECTIONPATHID)
  28.                     LEFT JOIN MASTERDATA.STUDYPROGRAMCLASSIFICATION C ON (S.CLASSIFICATIONID=C.CLASSIFICATIONID)
  29.                     LEFT JOIN (SELECT STUDENTID, ACADEMICCOUNCILNAME, YUDISIUMSTATUS FROM ACADEMIC.ACADEMICSTATUS WHERE ACADEMICCOUNCILNAME IN ('DIPLOMA','SARJANA','MAGISTER') AND ACADEMICCOUNCILDATE IS NOT NULL) AC ON (S.STUDENTID=AC.STUDENTID)
  30.                     LEFT JOIN (
  31.                         SELECT
  32.                             STUDENTID,
  33.                             TRIM (ETHNIC) ETHNIC
  34.                         FROM
  35.                             ADMISSION.PARTICIPANT
  36.                         JOIN ADMISSION.ADDITIONALPARTICIPANTDATA USING (PARTICIPANTID)
  37.                         WHERE
  38.                             STUDENTID IS NOT NULL
  39.                     ) AE ON (S.STUDENTID = AE.STUDENTID)
  40.                     WHERE 1=1
  41.                     ORDER BY S.STUDENTID ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement