增加不存在的数据
?
select cs.site_id siteID, cs.site_name siteName, cch.category_name categoryDesc, cwi.icam_bucket_id bucketId, cwi.work_item_desc servItemDesc, --to_char(ce.event_start_dt, 'yyyy') year, sub_years. year, sum((case when to_char(ce.event_start_dt, 'yyyy') = sub_years. year then nvl(decode(ces.actualize_ind, 'P', ces.proj_cost, 'A', ces.act_cost + ces.outstand_cost, 'L', ces.act_cost, 0), 0) / TO_NUMBER(1000000) else 0 end)) price_new, -- sum((case when to_char(ce.event_start_dt, 'yyyy') = sub_years. year then nvl(decode(ces.actualize_ind, 'P', ces.proj_cost, 'A', ces.act_cost + ces.outstand_cost, 'L', ces.act_cost, 0), 0) / TO_NUMBER(1000000) else 0 end)) cost_new from COST_MDL_STANDALONE_SERVICE mss, cost_site cs, COST_WORK_ITEM cwi, cost_work_item_category cwic, cost_category_head cch, cost_event_service ces, cost_event ce, (select level + 2010 - 1 year from dual connect by level <= (2021 - 2010 + 1)) sub_years where ces.mdl_ver_id = 119545 and ces.mdl_standalone_serv_id = mss.mdl_standalone_serv_id and ces.event_id = ce.event_id and ces.mdl_ver_id = ce.mdl_ver_id and cch.category_head_id = cwic.category_head_id and mss.mdl_ver_id = ces.mdl_ver_id and cs.site_id = mss.site_id and cwi.work_item_id = mss.work_item_id and cwic.work_item_category_id = cwi.work_item_category_id--and ce.event_typ_id = 5 --and cwi.work_item_desc = 'Vehicle Services' group by cs.site_id, cs.site_name, cch.category_name, cwi.icam_bucket_id, cwi.work_item_desc, sub_years. year
?
