Solved

oracle query

Posted on 2013-05-14
9
301 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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 48

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 48

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

Independent Software Vendors: 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!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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