Solved

oracle query

Posted on 2013-05-14
9
304 Views
Last Modified: 2013-05-15
select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive,
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive,a.audit_key,drive_date

COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW
Coach|1543|1261
Mini|353|273
Inside|5329|4584

If I want to add the drive_id and audit_key then I will get more lines of data.
Is there a way to avoid it, but still use in the query but only get the data like above?

select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive,
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw,
          a.audit_key.a.drive_id
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive,a.audit_key,drive_date
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39166028
If you just want the data as you have right now and add additional columns, you need to do aggregation on those columns?
Do you want the max audit_key/drive_id?  Can you post some expected result?
0
 
LVL 6

Author Comment

by:anumoses
ID: 39166053
COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW|DRIVE_DATE|AUDIT_KEY
Coach|21|17|4/1/2013|167544
Mini|11|7|4/1/2013|167618
Mini|12|13|4/1/2013|170063
Inside|25|7|4/1/2013|166278
Inside|25|15|4/1/2013|167315
Inside|31|24|4/1/2013|167561
Coach|50|14|4/2/2013|167587
Coach|12|12|4/2/2013|167614
Coach|46|32|4/2/2013|167629
Mini|9|6|4/2/2013|167419
Coach|34|32|4/3/2013|167332
Coach|15|8|4/3/2013|167569
Coach|30|35|4/3/2013|167570
Coach|16|14|4/3/2013|167571
Coach|12|8|4/3/2013|170099
Mini|13|12|4/3/2013|167572
Inside|25|18|4/3/2013|167287
Inside|31|26|4/3/2013|167299
Inside|24|12|4/3/2013|167568
Inside|45|33|4/3/2013|168345


But expected is

COACH_DRIVE|TOT_PROJECTION|TOT_ACTUAL_DRAW
Coach|1543|1261
Mini|353|273
Inside|5329|4584
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39166140
I think you already have the expected result. Do you want DRIVE_DATE|AUDIT_KEY in the result set? If yes, which value for these columns? max/min??
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:anumoses
ID: 39166150
I do not want any values to be displayed. The reason is I have to write another function in na report based on this field. Thats the reason I want the drive_date and audit key. But if I choose then I dont get sum of projection and actual_drive for the coach, mini and inside.
0
 
LVL 6

Author Comment

by:anumoses
ID: 39166164
select new_value
   into v_new
 from blood_drives_audit
where audit_key = :audit_key
       and column_name = 'projection'
       and audit_insert_date <= (:drive_date - :p_days1)
       and audit_insert_date = (select max(audit_insert_date)
                                  from blood_drives_audit cc
                                      where cc.audit_key = :audit_key
                                               and cc.column_name = 'projection');

If I choose the drive_date and audit key, I want to use them in this query. Is there a way to add this query in the first one?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 39166170
I don't know your requirement. Check this if it helps you.
select CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
          END coach_drive, 
          sum(a.projection) tot_projection,
          sum(nvl(a.actual_draw,0) ) tot_actual_draw,
          max(a.audit_key) audit_key,
		  max(a.drive_id) drive_id
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0 
  and a.drive_cancelled is null
  and a.drive_date between '01-apr-2013' and '30-apr-2013'
group by coach_drive

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39167183
is there a way to (include several other items of data) but retain the exact same output
No
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39167186
Coach|12|12|4/2/2013|167614
Coach|12|8|4/3/2013|170099
Coach|15|8|4/3/2013|167569
Coach|16|14|4/3/2013|167571
Coach|21|17|4/1/2013|167544
Coach|30|35|4/3/2013|167570
Coach|34|32|4/3/2013|167332
Coach|50|14|4/2/2013|167587
Coach|46|32|4/2/2013|167629 ?

Coach|1543|1261

Inside|24|12|4/3/2013|167568
Inside|25|15|4/1/2013|167315
Inside|25|18|4/3/2013|167287
Inside|25|7|4/1/2013|166278
Inside|31|24|4/1/2013|167561
Inside|31|26|4/3/2013|167299
Inside|45|33|4/3/2013|168345 ?

Inside|5329|4584

Mini|11|7|4/1/2013|167618
Mini|12|13|4/1/2013|170063
Mini|9|6|4/2/2013|167419
Mini|13|12|4/3/2013|167572 ?

Mini|353|273

which items of extra data would be required, the italic ones?
(italics applied to the 'most recent date, and highest audit id')
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39169176
thanks
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

695 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