Compco Sample

select substr(spriden_id, -7)              id,
       spriden_last_name                   LastName,
       spriden_first_name                  FirstName,
       goremal_email_address               Email,
       null                                Dorm,
       null                                Room,
       nvl2(st.gorirol_role, 1, 0)         ActiveStudent,
       nvl2(em.gorirol_role, 1, 0)         ActiveEmployee,
       nvl2(nd.gorirol_role, 1, 0)         ActiveAffiliate
from spriden
left outer join gorirol st
  on st.gorirol_pidm = spriden_pidm
  and st.gorirol_role = 'STUDENT'
left outer join gorirol em
  on em.gorirol_pidm = spriden_pidm
  and em.gorirol_role = 'EMPLOYEE'
left outer join gorirol nd
  on nd.gorirol_pidm = spriden_pidm
  and nd.gorirol_role in ('NON_DREW_FULL', 'NON_DREW_LIMITED')
left outer join goremal
  on goremal_pidm = spriden_pidm
  and goremal_emal_code = 'DREW'
  and goremal_status_ind = 'A'
where spriden_change_ind is null
and spriden_pidm in (select gorirol_pidm
                     from gorirol
                     where gorirol_role in ('STUDENT', 'EMPLOYEE', 'NON_DREW_FULL',
                                            'NON_DREW_LIMITED'))
                                            
union all

select substr(spriden_id, -7) || 'S'       id,
       spriden_last_name                   LastName,
       spriden_first_name                  FirstName,
       goremal_email_address               Email,
       null                                Dorm,
       null                                Room,
       nvl2(st.gorirol_role, 1, 0)         ActiveStudent,
       nvl2(em.gorirol_role, 1, 0)         ActiveEmployee,
       nvl2(nd.gorirol_role, 1, 0)         ActiveAffiliate
from spriden
left outer join gorirol st
  on st.gorirol_pidm = spriden_pidm
  and st.gorirol_role = 'STUDENT'
left outer join gorirol em
  on em.gorirol_pidm = spriden_pidm
  and em.gorirol_role = 'EMPLOYEE'
left outer join gorirol nd
  on nd.gorirol_pidm = spriden_pidm
  and nd.gorirol_role in ('NON_DREW_FULL', 'NON_DREW_LIMITED')
left outer join goremal
  on goremal_pidm = spriden_pidm
  and goremal_emal_code = 'DREW'
  and goremal_status_ind = 'A'
where spriden_change_ind is null
and spriden_pidm in (select gorirol_pidm
                     from gorirol
                     where gorirol_role = 'STUDENT')
and spriden_pidm in (select gorirol_pidm
                     from gorirol
                     where gorirol_role = 'EMPLOYEE');