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
 

 

Select maximum value of Sequence Number for particular

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

New User


Joined: 16 Jun 2009
Posts: 1
Location: Chennai

PostPosted: Fri Jan 31, 2014 8:11 am    Post subject: Select maximum value of Sequence Number for particular
Reply with quote

Hi,
I am looking for a query which selects the maximum value of sequence number for various file id Prefix.

Here is the query which I have written but I its not working. Could some one please help me in resolving this issue?

SELECT A.FILE_TYPE
,A.FILE_ID_SUFFIX
,A.RUN_DATE_TIME
,A.FILE_SEQ_NUMBER
,A.FILE_DESCRIPTION
,A.TRANSFER_DATE_TIME
,A.TRANSFR_RESULT_IND
A.RECORD_COUNTER
FROM DPAXGA3.TAX523 A
INNER JOIN
( SELECT MAX(FILE_SEQ_NUMBER) MAX_SEQ_NUM, FILE_ID_SUFFIX
FROM DPAXGA3.TAX523
WHERE FILE_TYPE = 'SHEC'
AND FILE_ID_SUFFIX IN ('030','004','025')
GROUP BY FILE_ID_SUFFIX
) B ON A.FILE_TYPE = 'SHEC'
AND A.FILE_ID_SUFFIX = B.FILE_ID_SUFFIX
AND A.FILE_SEQ_NUMBER = B.MAX_SEQ_NUM
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 31, 2014 1:32 pm    Post subject: Re: Select maximum value of Sequence Number for particular
Reply with quote

Vijayshyale wrote:
... its not working. ... this issue ...

Not exactly the best description. What's the problem ?
Syntax error ?
no results ?
wrong results ?
time-out or locking problem ?

but anyhow : the psychic force is strong today and here's A solution for AN issue.
Code:
SELECT A.FILE_TYPE                                           
      ,A.FILE_ID_SUFFIX                                       
      ,A.RUN_DATE_TIME                                       
      ,A.FILE_SEQ_NUMBER                                     
      ,A.FILE_DESCRIPTION                                     
      ,A.TRANSFER_DATE_TIME                                   
      ,A.TRANSFR_RESULT_IND                                   
      ,A.RECORD_COUNTER                                       
 FROM DPAXGA3.TAX523 A                                       
WHERE A.FILE_TYPE      = 'SHEC'                               
  AND A.FILE_ID_SUFFIX IN ('030','004','025')                 
  AND A.FILE_SEQ_NUMBER = ( SELECT MAX(FILE_SEQ_NUMBER)       
                     FROM DPAXGA3.TAX523 B                   
                    WHERE B.FILE_TYPE      = A.FILE-TYPE     
                      AND B.FILE_ID_SUFFIX = A.FILE_ID_SUFFIX)
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 SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts what is meaning of the number in dcl Martylin PL/I & Assembler 2 Thu Mar 09, 2017 7:18 am


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