Penn Computing
Computing Menu Computing A-Z
Computing Home Information Systems & Computing Penn


Merged and Deleted Penn IDs

In order to find the Penn ID into which a duplicate record has been merged, query the view COMADMIN.CHANGED_PENN_ID:

Column Name Data Type Null? Description
V_SUBSUMING_PENN_ID NUMBER (8) N The surviving Penn ID
V_SUBSUMED_PENN_ID NUMBER (8) N The merged Penn ID
V_MERGED_DATE DATE N Date the merge was performed
V_MERGED_COMMENTS VARCHAR2 (999 Byte) Y Comments about the merge
V_REVERSED_DATE DATE Y Date the merge was reversed (cancelled)
V_REVERSED_COMMENTS VARCHAR2 (999 Byte Y Comments about the reversal

Merged Penn IDs

The following query will return all rows for merges that have not been reversed:

select v_subsuming_penn_id, v_merged_date, v_merged_comments
from COMADMIN.CHANGED_PENN_ID
where v_reversed_date is null
order by v.subsuming_penn_id

Sometimes, more than one duplicate record has been merged. A query searching for possible multiple merged Penn IDs requires the view to be joined to itself, as in this query:

select a.v_subsuming_penn_id as "Current and Surviving Penn ID",
a.v_subsumed_penn_id as "Merged Penn ID",
b.v_subsuming_penn_id as "Prior Surviving Penn ID",
b.v_subsumed_penn_id as "Prior Merged Penn ID"
from COMADMIN.CHANGED_PENN_ID a,
COMADMIN.CHANGED_PENN_ID b
where a.v_subsumed_penn_id = b.v_subsuming_penn_id
and a.v_reversed_date is null
and b.v_reversed_date is null
and a.v_subsuming_penn_id <> a.v_subsumed_penn_id

We see in the query results:

Current and Surviving Penn ID Merged Penn ID Prior Surviving Penn ID Prior Merged Penn ID
99999999 88888888 88888888 77777777

The surviving PennID is 99999999, into which 8888888 was merged, into which 77777777 was merged.

Any script searching for merged PennIDs should loop through the condition subsumed_penn_id = a subsuming_penn_id until subsumed_penn_id != a subsuming_penn_id.

Deleted Penn IDs

Sometimes a duplicate ID will be identified before any significant data has been entered on the record and can be deleted immediately. In this case, the view will be populated as follows:

SELECT *
FROM COMADMIN.CHANGED_PENN_ID
WHERE V_SUBSUMING_PENN_ID = V_SUBSUMED_PENN_ID
AND V_REVERSED_DATE IS NULL

Any questions about merged Penn IDs should be directed to penncommunity@isc.upenn.edu,

Contact: penncommunity@isc.upenn.edu