I have this query that is using a PIVOT. this works fine however it appears to be that when I am adding more on the for in clause I am getting an error
ORA-03113: end-of-line on communication channel
Process ID: 12237774
for example:
for ca_fy in ('2009','2010','2011','2012','2013','2014','2015','2016','2017')
I added another year 2008
for ca_fy in ('2008','2009','2010','2011','2012','2013','2014','2015','2016','2017')
it is getting an error ORA-03113
this is the query:
select *
from (select *
from ( -- municipality
select lm.muni muni_id,
lm.region_id region_num,
lm.county_id,
lc.name county_name,
lmt.nameof||lm.name muni_name,
nvl(lmt.osc_class,'00') osc_class,
lm.mou_type,
lcl1.lookup_desc mou_desc,
lm.redc_type,
lcl2.lookup_desc redc_desc
from lps.muni lm,
lps.muni_type lmt,
lps.county lc,
lps.lps_common_lookup lcl1,
lps.lps_common_lookup lcl2
where lm.type = lmt.id
and lm.county_id = lc.id
and lm.mou_type = lcl1.lookup_code
and lcl1.lookup_name = 'MOU_TYPE'
and lm.redc_type = lcl2.lookup_code
and lcl2.lookup_name = 'REDC_TYPE') ivcm, -- inline view for municipality
(-- allotment and payment
select ivca.*, ivcp.pay_amt
from (select ca.muni ca_muni_id,
ca.fy ca_fy,
sum(ca.amount) allot_amt
from chips.allot ca
where ca.type = '1' -- capital allotment for the given fy
and ca.cycle = '1' -- initial allotment for the given fy
group by ca.muni, ca.fy
order by ca.fy, ca.muni) ivca, -- inline view for allotment
(select ivpr.muni_id cp_muni_id,
ivpr.fy cp_fy,
sum(ivpr.pay_amt) pay_amt
from (select cr.muni muni_id,
cr.fy fy,
sum(cr.amount) pay_amt
from chips.request cr
where cr.pay_id is not null
group by cr.muni, cr.fy) ivpr -- inline view payment request
group by ivpr.muni_id, ivpr.fy
order by ivpr.fy, ivpr.muni_id ) ivcp -- inline view for payment
where ivca.ca_muni_id = ivcp.cp_muni_id
and ivca.ca_fy = ivcp.cp_fy (+)) ivap -- inline view for allotment and payment
where ivcm.muni_id = ivap.ca_muni_id
and to_number(ivap.ca_fy) between 2008 and 2017 and ivcm.muni_id = decode('002034',null,ivcm.muni_id,'002034')) ivmap
pivot (sum(nvl(ivmap.allot_amt,0)) as ca, sum(nvl(ivmap.pay_amt,0)) as cp
for ca_fy in ('2009','2010','2011','2012','2013','2014','2015','2016','2017'))
ORDER BY ca_muni_id
this is the output:
MUNI_ID REGION_NUM COUNTY_ID COUNTY_NAME MUNI_NAME OSC_CLASS MOU_TYPE MOU_DESC REDC_TYPE REDC_DESC CA_MUNI_ID '2009'_CA '2009'_CP '2010'_CA '2010'_CP '2011'_CA '2011'_CP '2012'_CA '2012'_CP '2013'_CA '2013'_CP '2014'_CA '2014'_CP '2015'_CA '2015'_CP '2016'_CA '2016'_CP '2017'_CA '2017'_CP
------- ---------- --------- ----------- ------------------------------------------------------------- --------- ---------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
002034 11 00 (N.Y. City) City of New York 00 1 NYC 1 NYC 002034 75471473.0 83098840.7 75471473.0 76299063.2 75471473.0 68413737.3 75471473.0 122569401. 85971408.7 65691749.1 85971408.7 79323901.1 85971408.7 51885334.3 85971408.7 95746494.8 85971408.7 22619702.2