File indexing completed on 2023-03-17 11:15:09
0001 CREATE MATERIALIZED VIEW ECALPEDESTALS
0002 AS
0003
0004 SELECT
0005 miov.iov_id iov_value_id,
0006 riov.run_num time
0007 FROM
0008
0009 (SELECT iov_id, run_iov_id,
0010 MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0011 FROM mon_run_iov@cmsomds GROUP BY iov_id, run_iov_id) miov
0012 JOIN run_iov@cmsomds riov ON miov.run_iov_id = riov.iov_id
0013 JOIN run_tag@cmsomds rtag ON riov.tag_id = rtag.tag_id
0014 JOIN run_type_def@cmsomds rdef ON rtag.run_type_id = rdef.def_id
0015 JOIN location_def@cmsomds ldef ON rtag.location_id = ldef.def_id
0016 WHERE
0017 ldef.location='H4'
0018 AND rdef.run_type='PEDESTAL'
0019 AND rdef.config_tag='PEDESTAL-STD'
0020 AND rdef.config_ver=1
0021 ;
0022
0023
0024 CREATE MATERIALIZED VIEW ECALPEDESTALS_ITEM
0025 AS
0026 SELECT
0027 dat.iov_id iov_value_id,
0028
0029 ROW_NUMBER() OVER (PARTITION BY dat.iov_id ORDER BY dat.logic_id ASC) pos,
0030 cv.id1 det_id,
0031 dat.ped_mean_g12 mean_x12,
0032 dat.ped_rms_g12 rms_x12,
0033 dat.ped_mean_g6 mean_x6,
0034 dat.ped_rms_g6 rms_x6,
0035 dat.ped_mean_g1 mean_x1,
0036 dat.ped_rms_g1 rms_x1
0037 FROM
0038
0039 (SELECT iov_id, run_iov_id,
0040 MAX(subrun_num) KEEP (DENSE_RANK FIRST ORDER BY subrun_num ASC)
0041 FROM mon_run_iov@cmsomds GROUP BY iov_id, run_iov_id) miov
0042 JOIN run_iov@cmsomds riov ON miov.run_iov_id = riov.iov_id
0043 JOIN run_tag@cmsomds rtag ON riov.tag_id = rtag.tag_id
0044 JOIN run_type_def@cmsomds rdef ON rtag.run_type_id = rdef.def_id
0045 JOIN location_def@cmsomds ldef ON rtag.location_id = ldef.def_id
0046 JOIN mon_pedestals_dat@cmsomds dat ON dat.iov_id = miov.iov_id
0047 JOIN channelview@cmsomds cv ON cv.logic_id = dat.logic_id
0048 WHERE
0049 cv.name='Offline_det_id'
0050 AND ldef.location='H4'
0051 AND rdef.run_type='PEDESTAL'
0052 AND rdef.config_tag='PEDESTAL-STD'
0053 AND rdef.config_ver=1
0054 ;