SELECT (CASE WHEN tabl.t1 is not null then tabl.t1
WHEN tabl.t2 is not null then tabl.t2
WHEN tabl.t3 is not null then tabl.t3
WHEN tabl.t4 is not null then tabl.t4
WHEN tabl.t5 is not null then tabl.t5
END) EC_TIME,
( CASE WHEN tabl.c1 is not null then tabl.c1
WHEN tabl.c2 is not null then tabl.c2
WHEN tabl.c3 is not null then tabl.c3
WHEN tabl.c4 is not null then tabl.c4
WHEN tabl.c5 is not null then tabl.c5
END) EC_CITY_NAME ,
tabl.mrtu CMR_TOTALCOMSUPTION,
tabl.mrau CMR_AIRCONDITIONING,
tabl.mrmu CMR_PRIMARYFACILITY,
tabl.mrmus CMR_LIGHTINGANDOTHERS,
tabl.stu OWNBASE_TOTALCOMSUPTION,
tabl.sau OWNBASE_AIRCONDITIONING,
tabl.smu OWNBASE_PRIMARYFACILITY ,
tabl.sgu OWNBASE_LIGHTINGANDOTHERS,
tabl.b1tu MAMAGEMENTHOUSE_TOTALUSAGE,
tabl.b2gu MAMAGEMENTHOUSE_GENERATORUSAGE,
tabl.b3tu OTHERS_TOTALUSAGE,
tabl.b3gu OTHERS_GENERATORUSAGE
FROM (
SELECT m.cityname c1, m.EC_ITEM_TIME t1, s.cityname c2, s.EC_ITEM_TIME t2, b1.cityname c3, b1.EC_ITEM_TIME t3, b2.cityname
c4, b2.EC_ITEM_TIME t4,b3.cityname c5, b3.EC_ITEM_TIME t5,
NVL(m.machineroomtotalusage,0) mrtu, NVL(m.machineroomairconusage,0) mrau,NVL(m.machineroommainusage,0) mrmu,
NVL(m.machineroomothersusage,0) mrmus,
NVL(s.sitetotalusage,0) stu, NVL(s.siteairconusage,0) sau, NVL(s.sitemainusage,0) smu,
NVL(s.sitegeneratorusage,0) sgu,
NVL(b1.totalusage,0) b1tu, NVL(b1.generator_usage,0) b1gu, NVL(b2.totalusage,0) b2tu,
NVL(b2.generator_usage,0) b2gu, NVL(b3.totalusage,0) b3tu,
NVL(b3.generator_usage, 0) b3gu
FROM(
SELECT sr.NAME cityname, to_char( rcm.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcm.TOTAL_USAGE) machineroomtotalusage, SUM(rcm.AIRCON_USAGE)
machineroomairconusage, SUM(rcm.MAIN_USAGE) machineroommainusage,
SUM(rcm.GENERATOR_USAGE+rcm.OTHER_USAGE) machineroomothersusage
FROM SYS_REGION sr
full join RES_MACHROOM rm
ON sr.CODE = rm.CITYID
join RPT_CAP_MACHROOM rcm
ON rcm.MACHROOMID = rm.ZGID
GROUP BY sr.NAME, rcm.READTIME
)m
full join
(
SELECT sr.NAME cityname,to_char( rcs.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcs.TOTAL_USAGE) sitetotalusage,
SUM(round(rcs.AIRCON_USAGE, 2)) as siteairconusage,
SUM(rcs.MAIN_USAGE) sitemainusage, SUM(rcs.GENERATOR_USAGE) sitegeneratorusage
FROM SYS_REGION sr
full join RES_BTS rb
ON sr.CODE = rb.CITYID
join RPT_CAP_BTS rcs
ON rcs.BTSID = rb.ZGID
GROUP BY sr.NAME, rcs.READTIME
)s
on m.cityname = s.cityname AND m.EC_ITEM_TIME = s.EC_ITEM_TIME
full join
(
SELECT sr.NAME cityname, to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
FROM SYS_REGION sr
full join RES_BUILD rbb
ON sr.CODE = rbb.CITYID
join RPT_CAP_BUILD rcb
ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='1'
GROUP BY sr.NAME, rcb.READTIME
) b1
on m.cityname = b1.cityname AND m.EC_ITEM_TIME = b1.EC_ITEM_TIME
full join
(
SELECT sr.NAME cityname, to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME, SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
FROM SYS_REGION sr
full join RES_BUILD rbb
ON sr.CODE = rbb.CITYID
join RPT_CAP_BUILD rcb
ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='3'
GROUP BY sr.NAME, rcb.READTIME
) b3
on m.cityname = b3.cityname AND m.EC_ITEM_TIME = b3.EC_ITEM_TIME
full join
(
SELECT sr.NAME cityname, to_char(rcb.READTIME ,'yyyymm') as EC_ITEM_TIME , SUM(rcb.TOTAL_USAGE) totalusage, SUM(rcb.GENERATOR_USAGE) generator_usage
FROM SYS_REGION sr
full join RES_BUILD rbb
ON sr.CODE = rbb.CITYID
join RPT_CAP_BUILD rcb
ON rcb.BUILDID = rbb.ZGID AND rcb.BUILD_TYPE='2'
GROUP BY sr.NAME, rcb.READTIME
) b2
on m.cityname = b2.cityname AND m.EC_ITEM_TIME = b2.EC_ITEM_TIME ) tabl