Subj: [datamgt] Re: Intralink SQL query for objects at a given Release Level  
Date: 10/1/2003 3:04:50 PM Eastern Daylight Time 
From: marcs_mailbox@yahoo.com 
To: datamgt@ptcuser.org 
Sent from the Internet (Details) 

> From: "Adkins Brian M" <AdkinsBrianM@JohnDeere.com>
> 
> I'm working on creating a SQL query to identify objects for 
> which the following is true:
> 
> An object has more than one PIV at a given Release Level within 
> a single Revision...
> 
> In other words, I need to check for objects with multiple 
> versions of an object at our "COMPLETE" Release Level for all 
> Revisions of that object...

This query will show all revisions/versions of all objects at 
the 'COMPLETE' release level:

column piname format a35
column pivrev format a6
column pivver format 99999
column rlname format a15

select piv.pivid,pi.piname,piv.pivrev,piv.pivver,rl.rlname 
  from
    pdm.PDM_PRODUCTITEM pi,
    pdm.PDM_BRANCH br,
    pdm.PDM_PRODUCTITEMVERSION piv,
    pdm.PDM_PIVRL pivrl,
    pdm.PDM_RELEASELEVEL rl
  where
    pi.piid=br.piid and 
    br.brid=piv.brid and 
    piv.pivid=pivrl.pivid and 
    pivrl.rlid=rl.rlid and 
    rl.rlname='COMPLETE' 
  order by piname,pivrev,pivver
  ;


This query will, for all objects, count all versions of a 
revision at the 'COMPLETE' release level, and only show 
objects that have more than one version:

column piname format a35
column pivrev format a6
column vercount format 99999
column rlname format a15

select 
    pi.piname, 
    piv.pivrev, 
    count(piv.pivver) vercount, 
    rl.rlname 
  from
    pdm.PDM_PRODUCTITEM pi,
    pdm.PDM_BRANCH br,
    pdm.PDM_PRODUCTITEMVERSION piv,
    pdm.PDM_PIVRL pivrl,
    pdm.PDM_RELEASELEVEL rl
  where
    pi.piid=br.piid and 
    br.brid=piv.brid and 
    piv.pivid=pivrl.pivid and 
    pivrl.rlid=rl.rlid and 
    rl.rlname='COMPLETE' 
  group by pi.piname,piv.pivrev,rl.rlname 
  having count(piv.pivver) > 1 
  order by pi.piname,piv.pivrev 
  ;


I can't promise you that it works flawlessly, but this should 
be very close to what you wanted, if I understood you correctly.


Good luck ....

Marc
--
Marc Mettes
CAD Support
CSC @ GDLS