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
 
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 Repeat Sequence Number Until change i... rahulabvp DFSORT/ICETOOL 6 Thu Mar 29, 2018 12:51 pm
No new posts SELECT TO MULIPLE OUTFIL speermb DFSORT/ICETOOL 5 Fri Mar 23, 2018 10:44 pm
No new posts Detect break in the sequence tuxama DFSORT/ICETOOL 6 Tue Mar 20, 2018 1:35 pm
No new posts add sequence number for duplicate rec... SRUTHI DRAVIDAMANI COBOL Programming 11 Thu Feb 22, 2018 5:37 pm
No new posts PARSE GDG name to separate generation... Abid Hasan SYNCSORT 2 Sun Feb 11, 2018 3:40 pm

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