I have a table that I have to arrange by fiscal year, with months going from April to March each year.
I have everything worked out except for how to get the fiscal year names as column headings. Even though I have to_char'd the values, I still get an ORA-1790 expressioon must have same datatype when I try to UNION ALL the headings on the rest of the data.
The two components work fine separately. I'm probably just missing something simple, but what? (the original problem has a table with 18 years of data -- I've given only three for brevity)
Thanks,
Jon
Desired output is:
M 2011/2012 2012/2013 2013/2014
1 25194 24716 24015
2 25275 24794 23916
3 25005 24475 23592
4 24770 24577 23471
5 24806 24470 23543
6 24795 24265 23606
7 24636 24318 23504
8 24664 24262 23307
9 24406 24119 23293
10 24717 24039 23355
11 24718 23910 23317
12 24637 23894
Table is:
create table rb_raw (m_date date,total_cases number(6));
insert into rb_raw values(to_date('01-JAN-11','dd-mon-yy'),25119);
insert into rb_raw values(to_date('01-FEB-11','dd-mon-yy'),25026);
insert into rb_raw values(to_date('01-MAR-11','dd-mon-yy'),25305);
insert into rb_raw values(to_date('01-APR-11','dd-mon-yy'),25194);
insert into rb_raw values(to_date('01-JUN-11','dd-mon-yy'),25005);
insert into rb_raw values(to_date('01-JUL-11','dd-mon-yy'),24770);
insert into rb_raw values(to_date('01-AUG-11','dd-mon-yy'),24806);
insert into rb_raw values(to_date('01-SEP-11','dd-mon-yy'),24795);
insert into rb_raw values(to_date('01-OCT-11','dd-mon-yy'),24636);
insert into rb_raw values(to_date('01-NOV-11','dd-mon-yy'),24664);
insert into rb_raw values(to_date('01-DEC-11','dd-mon-yy'),24406);
insert into rb_raw values(to_date('01-FEB-12','dd-mon-yy'),24718);
insert into rb_raw values(to_date('01-MAR-12','dd-mon-yy'),24637);
insert into rb_raw values(to_date('01-APR-12','dd-mon-yy'),24716);
insert into rb_raw values(to_date('01-JUN-12','dd-mon-yy'),24475);
insert into rb_raw values(to_date('01-JUL-12','dd-mon-yy'),24577);
insert into rb_raw values(to_date('01-AUG-12','dd-mon-yy'),24470);
insert into rb_raw values(to_date('01-SEP-12','dd-mon-yy'),24265);
insert into rb_raw values(to_date('01-OCT-12','dd-mon-yy'),24318);
insert into rb_raw values(to_date('01-DEC-12','dd-mon-yy'),24119);
insert into rb_raw values(to_date('01-JAN-13','dd-mon-yy'),24039);
insert into rb_raw values(to_date('01-FEB-13','dd-mon-yy'),23910);
insert into rb_raw values(to_date('01-MAR-13','dd-mon-yy'),23894);
insert into rb_raw values(to_date('01-APR-13','dd-mon-yy'),24015);
insert into rb_raw values(to_date('01-JUN-13','dd-mon-yy'),23592);
insert into rb_raw values(to_date('01-JUL-13','dd-mon-yy'),23471);
insert into rb_raw values(to_date('01-AUG-13','dd-mon-yy'),23543);
insert into rb_raw values(to_date('01-SEP-13','dd-mon-yy'),23606);
insert into rb_raw values(to_date('01-OCT-13','dd-mon-yy'),23504);
insert into rb_raw values(to_date('01-NOV-13','dd-mon-yy'),23307);
insert into rb_raw values(to_date('01-DEC-13','dd-mon-yy'),23293);
insert into rb_raw values(to_date('01-MAY-11','dd-mon-yy'),25275);
insert into rb_raw values(to_date('01-MAY-12','dd-mon-yy'),24794);
insert into rb_raw values(to_date('01-MAY-13','dd-mon-yy'),23916);
insert into rb_raw values(to_date('01-JAN-14','dd-mon-yy'),23355);
insert into rb_raw values(to_date('01-FEB-14','dd-mon-yy'),23317);
insert into rb_raw values(to_date('01-JAN-12','dd-mon-yy'),24717);
insert into rb_raw values(to_date('01-NOV-12','dd-mon-yy'),24262);
and my current query (with the heading part currently commented out):
with b as
(select to_char(basefisc - (18-y)) || '/' || to_char(basefisc - (17-y)) fyear,m_date,y,m,to_char(total_cases) total_cases
from
(select m_date,/*months_between(m_date,f.fystart) mb,*/floor((months_between(m_date,f.fystart)-1)/12)+18 y,
CASE WHEN extract(month from m_date) > 3 then extract(month from m_date)-3 else 9 + extract(month from m_date) end m,
extract(year from fystart) basefisc,total_cases
from rb_raw
cross join
(select case when extract(month from to_date(:current_month,'yyyymm')) < 3 then add_months(to_date(:current_month,'yyyymm'),-9- extract(month from to_date(:current_month,'yyyymm')))
else add_months(to_date(:current_month,'yyyymm'),-(extract(month from to_date(:current_month,'yyyymm'))-4))
end fystart
from dual) f
where floor(months_between(m_date,f.fystart)/12)+18 > 0
order by m_date)
)
--
select *
from
(/*(select *
from
(select 0 m,y,fyear
from b
)
pivot (max(fyear) for y in ( 16,17,18)) )
UNION ALL */
(select *
from
(select y,m,total_cases
from b
)
pivot (sum(total_cases) for y in (16,17,18)))
)
order by m
;