P5/21X/OMDSQuery.new_bug.sql

0001 select
0002          r.runnumber as rNb
0003        , m.modedescription as modeT
0004        , p.partitionname as pName
0005        -- , a.analysistype
0006        -- , r.starttime as run_start
0007        -- , s.statehistorydate as state_date
0008        -- , s.fecversionmajorid||'.'||s.fecversionminorid as run_fec
0009        -- , s.fedversionmajorid||'.'||s.fedversionminorid as run_fed
0010        -- , s.connectionversionmajorid||'.'||s.connectionversionminorid as run_conn
0011        -- , s2.statehistorydate as newstate_date
0012        , s2.fecversionmajorid||'.'||s2.fecversionminorid as new_fec
0013        , s2.fedversionmajorid||'.'||s2.fedversionminorid as new_fed
0014        , s2.connectionversionmajorid||'.'||s2.connectionversionminorid as new_conn
0015        , s2.dcuinfoversionmajorid||'.'||s2.dcuinfoversionminorid
0016         , r.runmode
0017   from
0018     run r
0019   , partition p
0020   , modetype m
0021   , statehistory s
0022   , statehistory s2
0023   where
0024       m.runmode=r.runmode
0025   and r.runmode !=21
0026   and s2.connectionversionmajorid!=2
0027   and r.partitionid=p.partitionid
0028   and r.partitionid=s.partitionid
0029   and r.statehistoryid=s.statehistoryid
0030   and s.partitionid = s2.partitionid
0031   and s2.statehistoryid = (
0032       select min(s3.statehistoryid)
0033       from statehistory s3
0034       where
0035           s3.statehistoryid > ( decode( s.statehistoryid, null, 0, s.statehistoryid) )
0036       and s3.partitionid = s.partitionid
0037   )
0038 and exists (
0039    select x.analysisid
0040    from analysis x
0041    where x.runnumber = r.runnumber
0042    and x.partitionid = r.partitionid
0043 ) ;