ODS – Sample Queries

Please read FERPA Policies prior to accessing Student Data

  • Graduating Students – Spring (Note: For Summer change spr to smr, and for Fall change spr to fal)

    SELECT D.FIRST_NAME,D.LAST_NAME,D.EMAIL,

    D.ADDR_1,D.ADDR_2,D.ADDR_3,D.CITY,D.STATE,D.NATION,D.ZIP

    FROM CSUBAN.CSUS_COMMENCEMENT_SPR C,

    CSUBAN.CSUG_GP_DEMO D

    WHERE C.PERSON_UID = D.PIDM AND (D.CONFIDENTIALITY_IND <> ‘Y’)

  • Get Information for Registered Students

    select b.csu_id ,b.first_name ,b.last_name,

    a.STUDENT_CLASS, a.STUDENT_CLASS_DESC,

    a.PRIMARY_MAJOR, a.PRIMARY_MAJOR_DESC,

    b.email, b.telephone, a.CONTINUOUS_REG,

    a.CREDITS_CE, a.CREDITS_RI, a.CREDITS_NON_CSU,

    a.CREDITS_SI, a.CREDITS_OTHER, a.CREDITS_TOTAL

    from csus_term_info_cur a

    join csug_gp_demo b on a.PIDM = b.pidm

    where b.confidentiality_ind <> ‘Y’

    order by b.legal_name

  • Get a student’s schedule for the current term

    SELECT a.ID, a.NAME, a.ACADEMIC_PERIOD,

    a.SUBJECT, a.COURSE_NUMBER,

    a.COURSE_SECTION_NUMBER, a.COURSE_REFERENCE_NUMBER

    FROM CSUBAN.CSUS_SECTION_INFO_CUR a

    order by a.SUBJECT ,a.COURSE_NUMBER ,a.COURSE_SECTION_NUMBER

  • Get all employees within Student Affairs (by mail code, ignoring students and temporary staff)

    SELECT LAST_NAME, FIRST_NAME, MIDDLE_NAME,

    CSU_ID,

    CASE WHEN PROPER_NAME_ADDRESS IS NULL THEN EMAIL_ADDRESS ELSE PROPER_NAME_ADDRESS END EMAIL,

    EMPLOYEE_TITLE,

    EMPLOYEE_DEPARTMENT,

    EMPLOYEE_DEPARTMENT_NUMBER,

    EMPLOYEE_TYPE

    FROM CSUBAN.CSUG_DIRECTORY_ALL

    WHERE ((EMPLOYEE_DEPARTMENT_NUMBER BETWEEN 8001 AND 8105) OR

    (EMPLOYEE_DEPARTMENT_NUMBER = 1032))

    AND (ACCOUNT_TYPE = ‘P’) AND (LIVE_LAG_START_DATE IS NULL)

    AND (DEAD_LAG_START_DATE IS NULL)

    AND (ACTIVE_FLAG_EIDDATA = ‘Y’)

    AND (EMPLOYEE_TYPE NOT LIKE ‘%Student Hourly%’)

    AND (EMPLOYEE_TYPE NOT LIKE ‘%Retiree%’)

    AND (EMPLOYEE_TYPE NOT LIKE ‘%Temporary%’)

    AND (EMAIL_ADDRESS IS NOT NULL)

    AND (ACTIVE_FLAG_EMAIL <> ‘N’)

    AND (ADMIN_ACCT_FLAG <> ‘Y’)

    ORDER BY LAST_NAME, FIRST_NAME

  • Get Student Information by eName (enrollment eligible) – NOTE: Replace the :ENAME variable with student eName

    SELECT CSU_ID, FIRST_NAME, LAST_NAME,

    STUDENT_CLASSIFICATION, STUDENT_MAJOR,

    CASE WHEN PROPER_NAME_ADDRESS IS NULL THEN EMAIL_ADDRESS ELSE PROPER_NAME_ADDRESS END EMAIL,

    CASE WHEN PHONE IS NULL THEN EMPLOYEE_PHONE ELSE PHONE END PHONE,

    STUDENT_ENROLLED_INDICATION

    FROM CSUG_DIRECTORY_PRIVACY

    WHERE ENAME = :ENAME