• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 5
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
Easiest is just MAX them:

select MONTH, MAX(ACTUAL2011), MAX(ACTUAL2012), MAX(ACTUAL2013), MAX(ACTUAL2014), MAX(BOOKED2014)
from proposed_annual_goal
 where rep_id = 11                        
group by month
 order by date_order
0
 
anumosesAuthor Commented:
Group by month -

ORA-00979: not a GROUP BY expression
0
 
slightwv (䄆 Netminder) Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
anumosesAuthor Commented:
No
Running the query you gave me
0
 
anumosesAuthor Commented:
Thanks got it.
0
 
slightwv (䄆 Netminder) Commented:
What was the issue?
0
 
anumosesAuthor Commented:
view had few more columns that had to be included.
0
 
anumosesAuthor Commented:
thanks
0
 
slightwv (䄆 Netminder) Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now