select 

ren.PINAME,
pivf.PIVFNAME,
br.BRPATH,
piv.PIVREV,
piv.PIVVER 

from 

(select distinct 

pi.PINAME 

from 

pdm.PDM_PRODUCTITEM pi, 
pdm.PDM_BRANCH br, 
pdm.PDM_PRODUCTITEMVERSION piv, 
pdm.PDM_PRODUCTITEMVERFILE pivf 

where pi.PIID=br.PIID 
and piv.BRID=br.BRID 
and piv.PIVID=pivf.PIVID 
and pi.PINAME!=pivf.PIVFNAME 

group by PINAME) 

ren, 
pdm.PDM_PRODUCTITEM pi, 
pdm.PDM_BRANCH br, 
pdm.PDM_PRODUCTITEMVERSION piv, 
pdm.PDM_PRODUCTITEMVERFILE pivf 

where pi.PINAME=ren.PINAME 
and pi.PIID=br.PIID 
and piv.BRID=br.BRID 
and piv.PIVID=pivf.PIVID 
order by PINAME,PIVFNAME

=================
SELECT PDM_PRODUCTITEM.PINAME, PDM_PRODUCTITEMVERSION.PIVREV, PDM_PRODUCTITEMVERFILE.PIVFMAPNAME, PDM_PRODUCTITEM.PITYPEID, PDM_PRODUCTITEMVERSION.RLID, PDM_RELEASELEVEL.RLNAME FROM PDM.PDM_PRODUCTITEM PDM_PRODUCTITEM, PDM.PDM_PRODUCTITEMVERFILE PDM_PRODUCTITEMVERFILE, PDM.PDM_PRODUCTITEMVERSION PDM_PRODUCTITEMVERSION, PDM.PDM_RELEASELEVEL PDM_RELEASELEVEL WHERE PDM_PRODUCTITEM.PINAME = PDM_PRODUCTITEMVERFILE.PIVFNAME AND PDM_PRODUCTITEMVERFILE.PIVID = PDM_PRODUCTITEMVERSION.PIVID AND PDM_PRODUCTITEMVERSION.RLID = PDM_RELEASELEVEL.RLID
=================

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='PRODUCTION' 
  order by piname,pivrev,pivver
  ;

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='PRODUCTION' order by piname,pivrev,pivver

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 order by piname,pivrev,pivver

select 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 pi.piname like '10001234' order by piname,pivrev,pivver

select 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 pi.piname LIKE '%10001234%' order by piname,pivrev,pivver