Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fecth first 10 rows only for different days in the = query

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

New User


Joined: 27 Nov 2007
Posts: 7
Location: Madrid

PostPosted: Fri Feb 18, 2011 2:46 pm    Post subject: Fecth first 10 rows only for different days in the = query
Reply with quote

Hi all,

I have a query that obtain the data from the 10 first users ordered by cpu time consumption from one day:

Code:
SELECT DATE,MVS_SYSTEM_ID,USER_ID,   
       CPU_TOTAL_SECONDS
  FROM DRL.MVS_STAT_TSO_D           
 WHERE DATE = '2011-02-17'
       AND MVS_SYSTEM_ID = 'CR01'   
       AND COD_BANCSB =  182         
 GROUP BY DATE, MVS_SYSTEM_ID,USER_ID
 ORDER BY CPU_TOTAL_SECONDS DESC
 FETCH FIRST 10 ROWS ONLY   


I would like, in the same query, to obtain the 10 first users ordered by cpu time consumption from EACH day where date is going to be from A to B like this:

WHERE DATE BETWEEN '2011-01-01' AND '2011-01-31'

I think that I have to develop a subquery or something like that, no?
I don't have much expertise in this area and would thank any help received.

Thanks in advance,
Guirao.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Feb 18, 2011 3:18 pm    Post subject:
Reply with quote

try UNION.
Back to top
View user's profile Send private message
Guirao

New User


Joined: 27 Nov 2007
Posts: 7
Location: Madrid

PostPosted: Fri Feb 18, 2011 4:51 pm    Post subject: About UNION
Reply with quote

Thanks for your answer Dick.

Well, if I use UNION i must indicate each date, and, if i want to put an interval using 'BETWEEN' this doesn't the effect that I want.

Apart from that, I want to order each data from cpu time grouping by the date and selecting the first 10 rows who have the most consumption from each date, and with 'UNION' first of all I must select the data and then group and order by I want.

Any suggestions?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Feb 18, 2011 9:51 pm    Post subject:
Reply with quote

Select somehow the 10 dates your interested in (using sysdummy1 or group by)


Code:
select date from tab1 where date between :min and :max group by date

join this with the select you already hae:

Code:
select * from
(select date from tab1 where date between :min and max group by date) A
join table (SELECT DATE,MVS_SYSTEM_ID,USER_ID,   
       CPU_TOTAL_SECONDS
  FROM DRL.MVS_STAT_TSO_D           
 WHERE DATE = a.date
       AND MVS_SYSTEM_ID = 'CR01'   
       AND COD_BANCSB =  182         
 GROUP BY DATE, MVS_SYSTEM_ID,USER_ID
 ORDER BY CPU_TOTAL_SECONDS DESC
 FETCH FIRST 10 ROWS ONLY    ) B on 1=1


I didn't test it, so it could contain syntax errors.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Feb 18, 2011 10:19 pm    Post subject:
Reply with quote

Hi Guirao,
Try something like this,
Code:
Select rank, date,MVS_SYSTEM_ID, USER_ID , CPU_TOTAL_SECONDS
(
 SELECT  DENSE_RANK ( ) OVER (PARTITION BY DATE, MVS_SYSTEM_ID, USER_ID
              ORDER BY CPU_TOTAL_SECONDS DESC) AS RANK
      , DATE                                             
      , MVS_SYSTEM_ID                                     
      , USER_ID                                           
      , CPU_TOTAL_SECONDS                                 
  FROM  DRL.MVS_STAT_TSO_D                               
  WHERE DATE BETWEEN '2011-01-01' AND '2011-01-31'       
    AND COD_BANCSB = 182
) as MVS
where rank <=10
;                                                         


Thanks,
Sushanth
Back to top
View user's profile Send private message
Guirao

New User


Joined: 27 Nov 2007
Posts: 7
Location: Madrid

PostPosted: Tue Feb 22, 2011 9:32 pm    Post subject: Fecth first 10 rows only for different days in the = query
Reply with quote

I like the two answers but I cannot correct the sintax errors... :-(


In the first case, I cannot reolve the syntax error, here is the error:

Code:
         NUMBER OF ROWS SELECTED 0                                             
--------+---------+---------+---------+---------+---------+---------+---------+
         SQL CODE IS   -199                                                   
DSNT408I SQLCODE = -199, ERROR:  ILLEGAL USE OF KEYWORD ORDER.  TOKEN ) UNION 
         EXCEPT WAS EXPECTED                                                   
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 506  0  0  -1  274  0 SQL DIAGNOSTIC INFORMATION         
DSNT416I SQLERRD    = X'000001FA'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000112'  X'00000000' SQL DIAGNOSTIC INFORMATION                   
OLLBACK                                                                       
--------+---------+---------+---------+---------+---------+---------+---------+
        NUMBER OF SQL STATEMENTS PROCESSED IS 1                               
        NUMBER OF INPUT RECORDS READ IS 11                                     
        NUMBER OF OUTPUT RECORDS WRITTEN IS 34                                 


In the second case, when I excute the SQL ( I have DB2 v8 for z/OS ), I don't know if this is valid in z/OS, this is the SQLCODE:

Code:
          NUMBER OF ROWS SELECTED 0                                       
---------+---------+---------+---------+---------+---------+---------+-----
          SQL CODE IS   -104                                               
 DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL "DENSE_RANK". SOME SYMBOLS
          THAT MIGHT BE LEGAL ARE: + - AS ORDER                           
 DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                         
 DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR             
 DSNT416I SQLERRD    = 502  0  0  -1  71  0 SQL DIAGNOSTIC INFORMATION     
 DSNT416I SQLERRD    = X'000001F6'  X'00000000'  X'00000000'  X'FFFFFFFF' 
          X'00000047'  X'00000000' SQL DIAGNOSTIC INFORMATION             
ROLLBACK                                                                   
---------+---------+---------+---------+---------+---------+---------+-----
         NUMBER OF SQL STATEMENTS PROCESSED IS 1                           
         NUMBER OF INPUT RECORDS READ IS 13                               
         NUMBER OF OUTPUT RECORDS WRITTEN IS 36                           


Thank you very much for your help.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Tue Feb 22, 2011 10:04 pm    Post subject:
Reply with quote

In the second case, the problem may be with the version of DB2 that you are using; ISTR that the DENSE_RANK function was only introduced with V9.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Feb 22, 2011 10:08 pm    Post subject:
Reply with quote

in the first case to "order by" in a subselect is also V9
Back to top
View user's profile Send private message
Guirao

New User


Joined: 27 Nov 2007
Posts: 7
Location: Madrid

PostPosted: Wed Feb 23, 2011 12:37 pm    Post subject: Fecth first 10 rows only for different days in the = query
Reply with quote

Ok dears,

If this functions only works with V9, I will wait to my DB2 administrators to migrate the version of the DB2 to v9, planned in this months.


I will post my results when I can repeat this SQL's.


Thank you another time for your time and for your help.

Have a nice day. :-)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Feb 23, 2011 8:17 pm    Post subject: Reply to: Fecth first 10 rows only for different days in the
Reply with quote

Good luck - we'll be here after the upgrade icon_smile.gif

d
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us