IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

INCLUDE COND with WHEN=GROUP


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sudhakarak

New User


Joined: 30 Apr 2020
Posts: 2
Location: INDIA

PostPosted: Mon Jun 21, 2021 6:55 pm
Reply with quote

I have a requirement where the records are present in the following format


Let's consider 1-5 bytes is person id. He might have multiple records. 7th byte is the determination indicator. If Person has 1 in the 7th position. I need to populate only record that has 1 in the 7th position. If a person doesn't have 1 record then I need to populate the M row. Please find the below input and expected output

INPUT:
Code:

11111|A|25
11111|0|35
11111|M|45
11111|1|46
22222|A|10
22222|M|05
22222|0|03


Expected OUTPUT:

Code:

11111|1|46
22222|M|05


I have tried using SORT WHEN=GROUP but I couldn't able to achieve it. Please share your thoughts.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Mon Jun 21, 2021 7:08 pm
Reply with quote

sudhakarak wrote:
I have tried using SORT WHEN=GROUP but I couldn't able to achieve it. Please share your thoughts.

Please, present here the results of your attempts, including the used code, the test input, the received output, and the error messages (if any).

This forum is used to give an advice, but not to do a job for other people, free of charge.
Back to top
View user's profile Send private message
sudhakarak

New User


Joined: 30 Apr 2020
Posts: 2
Location: INDIA

PostPosted: Mon Jun 21, 2021 7:54 pm
Reply with quote

Sorry for not adding my solutions. Out of many possibilities I got the expected result with two steps by doing the below but I'm guessing there might be a better solution than this.

Code:

//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN   DD *
11111|A|25
11111|0|35
11111|M|45
11111|1|46
22222|A|10
22222|M|05
22222|0|03
//SORTOUT  DD SYSOUT=*
//SYSIN DD *
 SORT FIELDS=(1,5,CH,A,7,1,CH,D)
 INCLUDE COND=(7,1,SS,EQ,C'1,M')
/*
//STEP02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN   DD *
11111|1|46
11111|M|45
22222|M|05
//SORTOUT  DD SYSOUT=*
//SYSIN DD *
 SORT FIELDS=(1,5,CH,A)
 SUM FIELDS=NONE
/*


Output:

Code:

11111|1|46
22222|M|05
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Mon Jun 21, 2021 8:11 pm
Reply with quote

Use the possibilities OUTFIL has to offer, namely SECTIONS with NODETAIL. You are pretty close to a good solution.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Mon Jun 21, 2021 8:22 pm
Reply with quote

Your criteria described a little doubtful.
As far as I understand them, it should work like this:
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,A)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END


Keep in mind: SORT executes the statements not in the order you have placed them, but as described in the SORT manual!
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Mon Jun 21, 2021 8:28 pm
Reply with quote

Leave off the SUM, it is already sorted for deduplication in SECTIONS.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Mon Jun 21, 2021 9:23 pm
Reply with quote

Joerg.Findeisen wrote:
Leave off the SUM, it is already sorted for deduplication in SECTIONS.

The sooner one gets rid of any unneeded stuff, the better (for any further processing).
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Mon Jun 21, 2021 9:34 pm
Reply with quote

sergeyken wrote:
The sooner one gets rid of any unneeded stuff, the better (for any further processing).

Tested and agreed.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Tue Jun 22, 2021 1:01 am
Reply with quote

Code:
//S1      EXEC PGM=SORT             
//SORTIN   DD  *                     
11111|A|25                           
11111|0|35                           
11111|M|45                           
11111|1|46                           
22222|A|10                           
22222|M|05                           
22222|0|03                           
33333|A|10                           
33333|0|03                           
44444|0|10                           
44444|M|05                           
44444|1|03                           
55555|1|10                           
55555|1|05                           
55555|M|03                           
//SORTOUT   DD SYSOUT=*             
//SYSOUT   DD  SYSOUT=*             
//SYSIN     DD *                     
  SORT FIELDS=(1,5,A,7,1,D,81,1,A),FORMAT=CH             
  INCLUDE COND=(7,1,CH,EQ,C'1',OR,7,1,CH,EQ,C'M')         
  INREC IFTHEN=(WHEN=(7,1,CH,EQ,C'1'),OVERLAY=(81:C'1')),
        IFTHEN=(WHEN=(7,1,CH,EQ,C'M'),OVERLAY=(81:C'2')) 
  OUTREC OVERLAY=(81:SEQNUM,3,ZD,RESTART=(1,5))           
  OUTFIL BUILD=(1,80),INCLUDE=(81,3,ZD,EQ,1)       

Output -
Code:
********************************* TOP OF DATA **********************************
11111|1|46                                                                     
22222|M|05                                                                     
44444|1|03                                                                     
55555|1|10                                                                     
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Tue Jun 22, 2021 7:23 am
Reply with quote

sergeyken wrote:
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,A)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END

Change HEADER3 to TRAILER3 to get the desired results.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue Jun 22, 2021 5:27 pm
Reply with quote

Joerg.Findeisen wrote:
sergeyken wrote:
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,A)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END

Change HEADER3 to TRAILER3 to get the desired results.

Oops, I messed up the priority between '1' and 'M' records...

Initially I coded SORT FIELDS=(…,7,1,CH,D) to make it in the proper order, but then something hit on my head, and I changed the sort order to 'A'...

This was my initial code, before my unneeded fix
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,D)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Wed Jun 23, 2021 8:10 pm
Reply with quote

sergeyken wrote:
Joerg.Findeisen wrote:
sergeyken wrote:
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,A)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END

Change HEADER3 to TRAILER3 to get the desired results.

Oops, I messed up the priority between '1' and 'M' records...

Initially I coded SORT FIELDS=(…,7,1,CH,D) to make it in the proper order, but then something hit on my head, and I changed the sort order to 'A'...

This was my initial code, before my unneeded fix
Code:
 INCLUDE COND=(7,1,SS,EQ,C'1M')   ignore all garbage immediately
 SORT FIELDS=(1,5,CH,A,           group by userid
              7,1,CH,D)           and order by priority
 SUM FIELD=NONE                   get rid of duplicates
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   leave only the best record
 END

Please test this , This is not producing the desired output as you still need TRAILER3.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Wed Jun 23, 2021 8:19 pm
Reply with quote

Code:
//STEPGRP  EXEC PGM=SORT
//SYNOUT   DD  SYSOUT=*
//SORTIN   DD  *
11111|A|25
11111|0|35
11111|M|45
11111|1|46
22222|A|10
22222|M|05
22222|0|03
//*
//SORTOUT  DD  SYSOUT=*
//*
//SYSIN    DD *
 INCLUDE COND=(7,1,SS,EQ,C'1M')   IGNORE ALL GARBAGE IMMEDIATELY
 SORT FIELDS=(1,5,CH,A,           GROUP BY USERID
              7,1,CH,D)           AND ORDER BY PRIORITY
 SUM FIELDS=NONE                   GET RID OF DUPLICATES
 OUTFIL NODETAIL,REMOVECC,
        SECTIONS=(1,5,HEADER3=(1,80))   LEAVE ONLY THE BEST RECORD
 END
//*

Code:
********************************* TOP OF DATA ********
11111|1|46
22222|M|05
******************************** BOTTOM OF DATA ******
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts ISRDSLST(3.4) Do not include addition... TSO/ISPF 0
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Compare latest 2 rows of a table usin... DB2 1
No new posts Reference for COND parameter. JCL & VSAM 1
No new posts Problem with IFTHEN=(WHEN=GROUP,BEGIN... DFSORT/ICETOOL 5
Search our Forums:

Back to Top