O2OFramework/sql/EcalPedestals_payload_o2o.sql

0001 /*
0002  *  EcalPedestals_payload_o2o()
0003  *
0004  *  EcalPedestals transform/transfer
0005  *  Parameters:  last_id:  The lower bounding IOV_VALUE_ID for objects to transfer
0006  */
0007
0008 CREATE OR REPLACE PROCEDURE EcalPedestals_payload_o2o (
0009   last_id IN NUMBER
0010 )
0011 AS
0012
0013 BEGIN
0014   INSERT INTO ecalpedestals
0015   (iov_value_id, time)
0016   SELECT
0017     miov.iov_id, riov.run_num
0018   FROM
0019     location_def@omds ldef, run_type_def@omds rdef, run_tag@omds rtag, run_iov@omds riov,
0020     /* Selects the mon_run_iov with the greatest subrun_num */
0021     (SELECT iov_id, run_iov_id,
0022        MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0023        FROM mon_run_iov@omds GROUP BY iov_id, run_iov_id) miov
0024   WHERE
0025       miov.run_iov_id = riov.iov_id
0026   AND riov.tag_id = rtag.tag_id
0027   AND rdef.def_id = rtag.run_type_id
0028   AND ldef.def_id = rtag.location_id
0029   AND ldef.location='P5_MT'
0030   AND rdef.run_type='PEDESTAL'
0031   AND miov.iov_id > last_id
0032 ;
0033
0034   INSERT INTO ecalpedestals_item
0035   (iov_value_id, pos, det_id, mean_x12, rms_x12, mean_x6, rms_x6, mean_x1, rms_x1)
0036   SELECT
0037     dat.iov_id,
0038     /* Creates a column of row numbers ordered by logic_id, for std::vec */
0039     ROW_NUMBER() OVER (PARTITION BY dat.iov_id ORDER BY dat.logic_id ASC),
0040     cv.id1,
0041     dat.ped_mean_g12,
0042     dat.ped_rms_g12,
0043     dat.ped_mean_g6,
0044     dat.ped_rms_g6,
0045     dat.ped_mean_g1,
0046     dat.ped_rms_g1
0047   FROM
0048     ecalpedestals iov, mon_pedestals_dat@omds dat, channelview@omds cv
0049   WHERE dat.iov_id = iov.iov_value_id
0050     AND cv.logic_id = dat.logic_id
0051     AND cv.name='Offline_det_id'
0052     AND iov.iov_value_id > last_id
0053 ;
0054 END;
0055 /
0056 show errors;