test/UploadDB/OMDSQuery.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        from
0016          run r
0017         , partition p
0018        , modetype m
0019        , analysis a
0020        , statehistory s
0021        , statehistory s2
0022        where
0023            m.runmode=r.runmode
0024        and r.runmode=2
0025        and r.partitionid=p.partitionid
0026        and r.partitionid=s.partitionid
0027        and r.statehistoryid=s.statehistoryid
0028        and s.partitionid = s2.partitionid
0029        and r.runnumber = a.runnumber
0030        and r.partitionid = a.partitionid
0031        and s2.statehistoryid = (
0032            select min(s3.statehistoryid) -- we use the statehistoryid which is better than the date (no unique contraint on date) because of "order" option in the sequence generator
0033            from statehistory s3
0034            where
0035                s3.statehistoryid != s.statehistoryid
0036            and s3.partitionid = s.partitionid
0037          -- the two following constraints on state date tend to increase the reliability of the result by
0038          -- reducing the time window in which the partition state may be updated with an overlap
0039            and s3.statehistorydate > s.statehistorydate
0040            and s3.statehistorydate > a.analysisdate
0041          -- device parameter upload must also be perform then, versions must change
0042            and (
0043                    (     a.analysistype = 'FASTFEDCABLING' -- connection run
0044                      and s3.connectionversionmajorid > s.connectionversionmajorid -- connection version change
0045                    ) or (
0046                          a.analysistype != 'FASTFEDCABLING' and s3.connectionversionmajorid = s.connectionversionmajorid -- connection version doesn't change
0047                    )
0048            )
0049            -- 'APVLATENCY','CALIBRATION','FASTFEDCABLING','FINEDELAY','OPTOSCAN','PEDESTALS','TIMING','VPSPSCAN'
0050            and (
0051                    (     a.analysistype in ('APVLATENCY','CALIBRATION','FINEDELAY','OPTOSCAN','TIMING','VPSPSCAN')
0052                      and s3.fecversionmajorid > s.fecversionmajorid
0053                    ) or (
0054                          a.analysistype not in ('APVLATENCY','CALIBRATION','FINEDELAY','OPTOSCAN','TIMING','VPSPSCAN')
0055                      and s3.fecversionmajorid >= s.fecversionmajorid
0056                    )
0057            )
0058            and (
0059                    (     a.analysistype in ('FASTFEDCABLING','PEDESTALS','TIMING')
0060                      and s3.fedversionmajorid > s.fedversionmajorid
0061                    ) or (
0062                          a.analysistype not in ('FASTFEDCABLING','PEDESTALS','TIMING')
0063                      and s3.fedversionmajorid >= s.fedversionmajorid
0064                    )
0065            )
0066        )
0067 --              and r.runnumber= 38400
0068 ;