asked on

oracle query - view

Created a view as below

create or replace view proposed_annual_goal
(MONTH,DATE_ORDER,ACTUAL2011,ACTUAL2012,ACTUAL2013,ACTUAL2014,BOOKED2014,REP_ID) as
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,
       sum(nvl(actual_draw,projection)) ,null,null,null,null,area_rep_no
from blood_drives
 where drive_date between '01-jan-2011' and '31-dec-2011'
   and 
   drive_cancelled is null
   and area_rep_no = 11
 group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
       to_char(drive_date,'MON')
union all  
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,null,
       sum(nvl(actual_draw,projection)),null,null,null,area_rep_no
from blood_drives
 where drive_date between '01-jan-2012' and '31-dec-2012'
   and drive_cancelled is null
   and area_rep_no = 11
 group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
       to_char(drive_date,'MON')	
union  all  
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,null,null,
       sum(nvl(actual_draw,projection)),null,null,area_rep_no
from blood_drives
 where drive_date between '01-jan-2013' and '31-dec-2013'
   and drive_cancelled is null
   and area_rep_no = 11
 group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
       to_char(drive_date,'MON')		  
union all
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,null,null,null,
       sum(nvl(actual_draw,projection)),null,area_rep_no
from blood_drives
 where drive_date between '01-jan-2014' and '31-dec-2014'
   and drive_cancelled is null
   and area_rep_no = 11
 group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
       to_char(drive_date,'MON')	
union  all  
select to_char(drive_date,'MON') date_label,
to_date(to_char(drive_date,'MON'),'MON') date_order,null,null,null,null,
       sum(projection),area_rep_no
from blood_drives
 where drive_date between '01-jan-2014' and '31-dec-2014'
   and drive_cancelled is null
   and area_rep_no = 11
 group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
       to_char(drive_date,'MON')

Open in new window

For reporting purpose I need these columns

select MONTH, ACTUAL2011, ACTUAL2012, ACTUAL2013, ACTUAL2014, BOOKED2014
from proposed_annual_goal
where rep_id = 11
order by date_order

The output I get is enclosed.

Expected

JAN 363 417 480 440 465
FEB 447 488 344 194 227
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
result-2nd-query.xls

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Group by month -

ORA-00979: not a GROUP BY expression

slightwv (䄆 Netminder)

I cannot see the syntax error. Are you selecting any other columns that you didn't post here?

All non-aggregate columns need to be grouped.

anumoses

ASKER

No
Running the query you gave me

anumoses

ASKER

Thanks got it.

slightwv (䄆 Netminder)

What was the issue?

anumoses

ASKER

view had few more columns that had to be included.

anumoses

ASKER

thanks

slightwv (䄆 Netminder)

For what it's worth, it looks like you can greatly simplify your view and make it much more efficient.

Take a look at this (no test data to test with so I just typed it in).

It should be close.

create or replace view proposed_annual_goal
 (MONTH,DATE_ORDER,ACTUAL2011,ACTUAL2012,ACTUAL2013,ACTUAL2014,BOOKED2014,REP_ID) as
 select to_char(drive_date,'MON') date_label,
 	to_date(to_char(drive_date,'MON'),'MON') date_order,
 	sum(case when drive_date between '01-jan-2011' and '31-dec-2011' then nvl(actual_draw,projection) end) actual2011
 	sum(case when drive_date between '01-jan-2012' and '31-dec-2012' then nvl(actual_draw,projection) end) actual2012
 	sum(case when drive_date between '01-jan-2013' and '31-dec-2013' then nvl(actual_draw,projection) end) actual2013
 	sum(case when drive_date between '01-jan-2014' and '31-dec-2014' then nvl(actual_draw,projection) end) actual2014
 	sum(case when drive_date between '01-jan-2014' and '31-dec-2014' then projection end) booked2014
 	area_rep_no
 from blood_drives
  where
    drive_cancelled is null
    and area_rep_no = 11
  group by area_rep_no, to_date(to_char(drive_date,'MON'),'MON'), 
        to_char(drive_date,'MON')
/

Open in new window