with finanImpact as (
select EXTRACT(year from fiwm.create_dt) as years, m.code as mcode, sum(fiwm.rate) as rate from MASTER.financial_impact_wfins_mapping fiwm
inner join MASTER.wf_instance wf on wf.sysid=fiwm.wf_instance and wf.template_id=4
inner join MASTER.wf_data_cr wdc on wdc.instance_id=fiwm.wf_instance
inner join PARAMETER.module m on m.sysid=wdc.module and m.sysid in (4,15,6)
group by EXTRACT(year from fiwm.create_dt),m.code
union (
select null, m1.code,0 from parameter.module m1 where m1.sysid in (4,15,6)
minus
select null, m.code as mcode, 0 from MASTER.financial_impact_wfins_mapping fiwm
inner join MASTER.wf_instance wf on wf.sysid=fiwm.wf_instance and wf.template_id=4
inner join MASTER.wf_data_cr wdc on wdc.instance_id=fiwm.wf_instance
inner join PARAMETER.module m on m.sysid=wdc.module and m.sysid in (4,15,6)
group by EXTRACT(year from fiwm.create_dt),m.code
)
)
select fi.years, fi.mcode, fi.rate from finanImpact fi
union
(
SELECT LEVEL+2008-1,'',0 AS ID
FROM DUAL
CONNECT BY LEVEL <= 2012-2008+1
minus(
select distinct lfi.years,'',0 from finanImpact lfi
)
)
returned result: ========================= 2008 null 0 2009 null 0 2010 EB 34640 2010 GOF 8660 2011 EB 103920 2011 GOF 12990 2012 null 0 null ASD 0 ========================= goal: ========================= 2008 ASD 0 2009 ASD 0 2010 EB 34640 2010 GOF 8660 2011 EB 103920 2011 GOF 12990 2012 ASD 0 =========================
As above, how to write a sql to achieve the "goal" section? Select rate for all selected modules and selected years no matter they got rate for that year or not.
The years and modules will be input by users, then the sql will return the sum rate for selected years and selected modules. For instance, Find rate for ASD,EB,GOF modules from 2008 till 2012, as the above