O2OFramework/sql/EcalPedestals_payload_o2o-H2.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@ecalh4db ldef, run_type_def@ecalh4db rdef, run_tag@ecalh4db rtag, run_iov@ecalh4db 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@ecalh4db 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='H2'
0030   AND rdef.run_type='PEDESTAL'
0031   AND miov.iov_id > last_id
0032 ;
0033
0034   /* The following query has been modified to include a horrible 3-time self-join of the channelview
0035      table with the purpose of remapping the data from SM1 to SM4 as required for ECAL-HCAL alignment
0036      cv1 maps the data to its native mapping
0037      cv2 maps the native mapping (cv1) to remap SM1 to SM4
0038      cv3 maps the altered mapping (cv2) to the det_id
0039    */
0040   INSERT INTO ecalpedestals_item
0041   (iov_value_id, pos, det_id, mean_x12, rms_x12, mean_x6, rms_x6, mean_x1, rms_x1)
0042   SELECT
0043     dat.iov_id,
0044     /* Creates a column of row numbers ordered by logic_id, for std::vec */
0045     ROW_NUMBER() OVER (PARTITION BY dat.iov_id ORDER BY dat.logic_id ASC),
0046     cv3.id1,
0047     dat.ped_mean_g12,
0048     dat.ped_rms_g12,
0049     dat.ped_mean_g6,
0050     dat.ped_rms_g6,
0051     dat.ped_mean_g1,
0052     dat.ped_rms_g1
0053   FROM
0054     ecalpedestals iov, mon_pedestals_dat@ecalh4db dat, channelview@ecalh4db cv1, channelview@ecalh4db cv2, channelview@ecalh4db cv3
0055   WHERE dat.iov_id = iov.iov_value_id
0056     AND cv1.logic_id = dat.logic_id
0057     AND cv1.name = 'EB_crystal_number'
0058     AND cv1.maps_to = 'EB_crystal_number'
0059     AND cv2.name = 'EB_crystal_number'
0060     AND cv2.maps_to = 'EB_crystal_number'
0061     AND cv2.id1 = 4 /* Translate to SM 4 */
0062     AND cv2.id2 = cv1.id2
0063     AND cv3.name='Offline_det_id'
0064     AND cv3.maps_to='EB_crystal_number'
0065     AND cv3.logic_id = cv2.logic_id
0066     AND iov.iov_value_id > last_id
0067 ;
0068 END;
0069 /
0070 show errors;