Tested in CONV:
create global temporary table tmp_drew_clean_students on commit preserve rows as select distinct sgbstdn_pidm, sgbstdn_term_code_eff from sgbstdn a where sgbstdn_stst_code = 'AS' and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff) from sgbstdn b join stvterm on b.sgbstdn_term_code_eff = stvterm_code where stvterm_start_date <= sysdate and b.sgbstdn_pidm = a.sgbstdn_pidm) and (select max(shrttrm_term_code) from shrttrm where shrttrm_pidm = sgbstdn_pidm) <= '201110' and sgbstdn_term_code_eff <= '201110'; insert into tmp_drew_clean_students select distinct sgbstdn_pidm, sgbstdn_term_code_eff from sgbstdn a where sgbstdn_stst_code = 'AS' and a.sgbstdn_term_code_eff = (select max(b.sgbstdn_term_code_eff) from sgbstdn b join stvterm on b.sgbstdn_term_code_eff = stvterm_code where stvterm_start_date <= sysdate and b.sgbstdn_pidm = a.sgbstdn_pidm) and sgbstdn_pidm not in (select shrttrm_pidm from shrttrm) and sgbstdn_term_code_eff <= '201110'; commit; select count(distinct sgbstdn_pidm) from tmp_drew_clean_students; select * from tmp_drew_clean_students; create global temporary table tmp_clean_sgbstdns on commit preserve rows as select * from sgbstdn a where exists (select 1 from tmp_drew_clean_students b where a.sgbstdn_pidm = b.sgbstdn_pidm and a.sgbstdn_term_code_eff = b.sgbstdn_term_code_eff); select * from tmp_clean_sgbstdns; update tmp_clean_sgbstdns set sgbstdn_stst_code = 'WD', sgbstdn_leav_code = 'L', sgbstdn_leav_from_date = sysdate, sgbstdn_leav_to_date = '31-DEC-2099', sgbstdn_data_origin = 'AutoReg', sgbstdn_user_id = 'CLEANUP', sgbstdn_term_code_eff = '201210'; insert into sgbstdn select * from tmp_clean_sgbstdns; commit;