Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Need SQL for selecting TOP 25 Jobs under TOP 25 Owners

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue May 01, 2007 8:51 pm    Post subject: Need SQL for selecting TOP 25 Jobs under TOP 25 Owners
Reply with quote

Hi,
My requirement is like this.
I have two tables
BatXMIT - having details about batch jobs, CPU time and CPU cost
APPLINV - Having Jobgroups, application Ids and owners

I need to get TOP OWNERS by SUM(CPUtime) and SUM(CPU COST)
which can be done by orderby desc on the sum fields and fetch only 25 rows. I am able to get the result till here.
In addition to that I need to get "TOP 25" jobs under each Owner.
So it will be 25 * 25 rows.

Can someone help me with this?
Any pointers will be of more help.
Thanks,
Viji
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue May 01, 2007 10:12 pm    Post subject:
Reply with quote

Can't you use the selected "TOP 25" owners as the selection criteria for the select of their jobs?
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Tue May 01, 2007 10:20 pm    Post subject: Re: Need SQL for selecting TOP 25 Jobs under TOP 25 Owners
Reply with quote

Yes. I did that. But, the result is the TOP 25 jobs of all of them and not per individual.
Say for Owner ABC, 10 top jobs exists and for owner XYZ 15 jobs.
then my result is top 25 from the list of 10+15 jobs and not per owner!

Hope I am clear.
Thanks,
Viji
Back to top
View user's profile Send private message
wanderer

Active User


Joined: 05 Feb 2007
Posts: 199
Location: Sri Lanka

PostPosted: Wed May 02, 2007 5:45 am    Post subject: Re: Need SQL for selecting TOP 25 Jobs under TOP 25 Owners
Reply with quote

You earlier said that you want 25*25 rows. Are you now saying that there may not be 25 job for each owner and some may have a maximum of 10 or 15 for example?

Please post column names for each of the two tables. And the relation(column name) by which the two tables are related to each other.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Conversion of DLI update Jobs as BMP ... Gopinath Shanmugam IMS DB/DC 3 Wed Dec 20, 2017 12:02 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
This topic is locked: you cannot edit posts or make replies. Selecting two copybooks of different ... Vignesh Sid COBOL Programming 8 Tue Sep 05, 2017 7:28 pm
No new posts self-restarting jobs in OPC jzhardy IBM Tools 1 Mon Aug 21, 2017 10:15 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us