Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

oracle query - view

Posted on 2014-07-11
9
Medium Priority
?
315 Views
Last Modified: 2014-07-11
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
Comment
Question by:anumoses
  • 5
  • 4
9 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40191068
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
 
LVL 6

Author Comment

by:anumoses
ID: 40191127
Group by month -

ORA-00979: not a GROUP BY expression
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40191142
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Author Comment

by:anumoses
ID: 40191147
No
Running the query you gave me
0
 
LVL 6

Author Comment

by:anumoses
ID: 40191154
Thanks got it.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40191157
What was the issue?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40191164
view had few more columns that had to be included.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40191193
thanks
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40191208
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month20 days, 20 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question