Orphaned Instances in Intralink Commonspace

A PTC/User post of mine from April 2003:

After noticing a few family table generics where instances were removed or omitted from the table in Pro/Engineer, I wrote the following sql query to find others in the Intralink Commonspace.

The logic works like this: look up the latest version of an instance name on the main branch, figure out the corresponding generic, and output if the associated generic is not the most recent generic.

If the most recent instance revision does not match up with the most recent generic revision, then the instance has been 'orphaned'. The output includes the revision and version of the instance and its associated generic.

set linesize 120
column INSTANCE format a35
column GENERIC format a35
column PIVREV format a6
column PIVVER format 99999

select
pii.PINAME as INSTANCE,
pivi.PIVREV,
pivi.PIVVER,
pig.PINAME as GENERIC,
pivg.PIVREV,
pivg.PIVVER
from
pdm.PDM_PRODUCTITEM pii,
pdm.PDM_BRANCH bri,
pdm.PDM_PRODUCTITEMVERSION pivi,
pdm.PDM_GENINSREL gis,
pdm.PDM_PRODUCTITEMVERSION pivg,
pdm.PDM_BRANCH brg,
pdm.PDM_PRODUCTITEM pig
where
bri.BRPATH='main' and
pii.PIID=bri.PIID and
bri.BRID=pivi.BRID and
bri.BRLASTVERID=pivi.PIVID and
pivi.PIVID=gis.INSTPIVID and
gis.GENPIVID=pivg.PIVID and
pivg.BRID=brg.BRID and
brg.PIID=pig.PIID and
brg.BRLASTVERID!=pivg.PIVID
order by GENERIC, INSTANCE
;