with -- phones sorted by pririty phone as (select /*+ INLINE */ sprtele_pidm, sprtele_tele_code, sprtele_phone_area, sprtele_phone_number, row_number() over (partition by sprtele_pidm, sprtele_tele_code order by sprtele_primary_ind, sprtele_activity_date desc) pri from sprtele where sprtele_status_ind is null), -- campus phones cmphone as (select /*+ INLINE */ sprtele_pidm, sprtele_phone_area, sprtele_phone_number from phone where sprtele_tele_code = 'CM' and pri = 1) select distinct spriden_id drew_id, spriden_last_name last_name, spriden_first_name first_name, spriden_mi middle_name, spbpers_pref_first_name pref_first_name, dept_desc department, sprtele_phone_area || sprtele_phone_number phone_number from spriden join gorirol on spriden_pidm = gorirol_pidm join cmphone on spriden_pidm = sprtele_pidm left outer join spbpers on spriden_pidm = spbpers_pidm left outer join v_drew_dir_departments on spriden_pidm = pidm where spriden_change_ind is null and gorirol_role in ('EMPLOYEE', 'NON_DREW_FULL');