MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Pick the result based on column values and priority rules

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
me_naveen
Beginner


Joined: 21 Dec 2007
Posts: 23
Topics: 12

PostPosted: Thu Mar 08, 2018 1:14 am    Post subject: Pick the result based on column values and priority rules Reply with quote

Hi,

I have below table EMP_COMM with the possible records as
Code:

EMP_NR    LINE_NR    COMM_TYP    COMM_M_ID
--------  ---------- ----------- --------------
1            1        X            aaa@ab.com
1            2        Y            bbb@ab.com
1            3        Z            ccc@ab.com
2            1        X            ddd@ab.com
2            2        X            eee@ab.com
3            1        X            fff@ab.com
4            1        Y            xxx@ab.com
4            2        Z            zzz@ab.com

Rules are:
In case COMM_TYP is filled for the records.
COMM_TYP X is first priority , Y is next priority and Z is the last priority
In case a Employee having 2 records are having same COMM_TYP , then next priority is the LINE_NR(EMP_NR is 2 in this case).That is highest LINE_NR record of the employee has to be picked for result.

Result of the above table looks like below and delete other not matching rows from the table : EMP_COMM

Possible Result:
------------------
Code:

EMP_NR     LINE_NR    COMM_TYP      COMM_M_ID
---------- ---------  ------------- -------------
1            1       X                aaa@ab.com
2            2       X                eee@ab.com
3            1       X                fff@ab.com
4            1       Y                xxx@ab.com

Please help me on this.
Thanks in advance.
nav
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12369
Topics: 75
Location: San Jose

PostPosted: Thu Mar 08, 2018 3:41 pm    Post subject: Reply with quote

me_naveen,

Try the following SQL which will give you the desired results.
Code:

SELECT A.EMP_NR                                             
      ,A.LINE_NR                                             
      ,A.COMP_TYP                                           
      ,A.COM_M_ID                                           
  FROM your_table   A                                         
      ,(SELECT EMP_NR                       AS EMP_NR       
              ,MIN(COMP_TYP)                AS COMP_TYP     
              ,MAX(LINE_NR)                 AS LINE_NR       
              ,ROWNUMBER()                                   
                 OVER (PARTITION BY EMP_NR) AS ROW#         
          FROM your_table                                   
         GROUP BY EMP_NR,COMP_TYP) B                         
WHERE A.EMP_NR   = B.EMP_NR                                 
  AND A.LINE_NR  = B.LINE_NR                                 
  AND A.COMP_TYP = B.COMP_TYP                               
  AND A.COMP_TYP > ' '                                       
  AND B.ROW#     = 1                                         
;   


PS: Start using the code tags in your posts so that it retains the spacing of the data. If your are not familiar with code tags then check this link explains in detail about code tags

https://www.mvsforums.com/helpboards/viewtopic.php?p=19031#19031
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group