Select highest sequence in SQL400

230 pts.
Tags:
#AS400 #RPGLE #as400
AS/400 SQL
AS400 iseries
SQLRPGLE
Please suggest a feasible solution to address this issue

Example output with duplicates (seq 133 and 160): Expectation is to pick only seq 160 by excluding 133:
                                                                                                                                   DSBLC DSRMB BDLDD  DSUSR    DSSEQ 
 4            55         190,613  CAVC01  133 
 4            55         190,613  CRPE01  160 

Query used in RPGLE program in a scroll-able cursor: 

select distinct DSBLC, DSRMB,DOlnm, DOfnm, SGTXT, BDldd,DSUSR,DSseq from DSfill09 

 join BDfil on BDBLC = DSBLC and BDRMB = DSRMB 
 join SGfil on SGBLC = DSBLC and SGRMB = DSRMB and SGseq = DSseq
 join DOfil on DOBLC = DSBLC and DORMB = DSRMB and DOnum = 9 

 where DScde = '***' and DSdte = 061319 and SGtxt like '%XYZ:%EXTRACT%' and DStyp = 'R' 
 Group by DSBLC, DSRMB, DOlnm, DOfnm, SGTXT, BDldd, DSUSR,DSseq Order by DSBLC, DSRMB
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You are missing the MAX(DSSEQ) in your select. 

Try something like this 
select distinct s.DSBLC, s.DSRMB,DOlnm, DOfnm, SGTXT, BDldd,s.DSUSR,s.maxseq from 
(select  DSBLC,DSRMB,BDLDD,DSUSR,max(DSSEQ) as maxseq from DSfill09
Group by DSBLC,DSRMB,BDLDD,DSUSR) s
 join BDfil on BDBLC = s.DSBLC and BDRMB = s.DSRMB 
 join SGfil on SGBLC = s.DSBLC and SGRMB = s.DSRMB and SGseq = s.maxseq
 join DOfil on DOBLC = s.DSBLC and DORMB = s.DSRMB and DOnum = 9 

 where DScde = '***' and DSdte = 061319 and SGtxt like '%XYZ:%EXTRACT%' and DStyp = 'R' 
 Order by DSBLC, DSRMB

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Hamsu
    I tried to use that too in a subquery but the result is coming as 

    DSBLC DSRMB BDLDD  DSUSR    DSSEQ 
     4            55         190,613  CRPE01  160 
     4            55         190,613  CAVC01  133 

    i.e. first the record with max(dsseq) is written and then the other one is also written. I will use this in a report so this is still resulting in a duplicate entry. 



    230 pointsBadges:
    report
  • Hamsu
    I have tried this one too but it doesn't look feasible either

    With T1 as (select DSdte,Max(DSseq) as maxseq        
     from DSFILL09 Group by DSdte)                       
    Select yt.DSBLC,yt.DSRMB,ms.maxseq   from DSfill09 yt
    join T1 ms on ms.DSdte = yt.DSdte                    
    join BDfil on BDBLC = yt.DSBLC                       
                and BDRMB = yt.DSRMB                     
    join DOfil on DOBLC = yt.DSBLC                       
                and DORMB =yt.DSRMB                      
                and DOnum = 9                            
    join SGfil on SGBLC = yt.DSBLC                       
                and SGRMB = yt.DSRMB                     
                and SGseq = ms.maxseq                    
    where SGtxt like '%XYZ:%EXTRACT% and yt.DStyp ='R' and yt.DSdte = 061319 and DScde ='***'                          
    order by DSBLC,DSRMB,DSseq                           
    230 pointsBadges:
    report
  • TheRealRaven
    Why doesn't it look feasible? The GROUP BY is necessary for the MAX() function to work as you want, but it's not clear what the GROUP BY group definition should be. What columns must be tested to get to "160"?
    33,670 pointsBadges:
    report
  • ToddN2000
    You may need a nested select, I don't think your code will work.
    In you join here, you are trying to use the max value.

    join SGfil on SGBLC = yt.DSBLC                       
                and SGRMB = yt.DSRMB                     
                and SGseq = ms.maxseq    

    You may want to do the initial select on your DS file doing the group by and returning the data with the MAX(DSSEQ).
    Then wrap this in another select doing the joins and other functions
    129,715 pointsBadges:
    report
  • BigKat
    Since the DSUSER values are not the same, what makes those two records duplicates??
    9,420 pointsBadges:
    report
  • ToddN2000
    Did not catch that BigKat. in that case change the grouping to get the max regardless of user to this 

    Group by DSBLC,DSRMB,BDLDD.

    Otherwise leave it as is and it will get the max bay all parameters including user.   
    Group by DSBLC,DSRMB,BDLDD,DSUSR

    129,715 pointsBadges:
    report
  • Hamsu
    Hello BigKat and ToddN2000 sorry i should have specified it, I meant duplicates based on DSBLC DSRMB and BDLDD. User could be same or different it doesn't matter. 

    I will try the solutions suggested. Thank you very much for your help.


    230 pointsBadges:
    report
  • TheRealRaven
    Your CTE above should be much closer if you change the SELECT and GROUP BY to specify DSBLC DSRMB and BDLDD rather than DSdte.

    But you might have wanted the MAX() by date (DSdte?). If that's true, then more explanation of what you need should be posted. The MAX() function will return the highest values from groups, but the grouping must be clearly defined.
    33,670 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: