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 

Conditional data selection

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


Joined: 26 Oct 2016
Posts: 8
Topics: 3

PostPosted: Wed May 31, 2017 7:54 am    Post subject: Conditional data selection Reply with quote

Hello,

This post is taken from another forum.
It poses an interesting challenge; posting here to expand the DFSORT expert solution base.

Requirement: If first 5 bytes (1,5) of Type is 30000, then for the corresponding code all types should be written to the output file.

Input data:

Code:

Type         Code
10000      12345
11000      12345
30000      12345
20000      12345
11000      45624
25000      45624
34000      33669
30000      33669


Output:

Code:
Type         Code
10000      12345
11000      12345
30000      12345
20000      12345
34000      33669
30000      33669


Reason why this is interesting is because it first requires to uniquely identify the 'Code' which are mapped to a certain 'Type'; once done, it again requires to GROUP all such 'Code' which were earlier tagged - this is where the task actually becomes a challenge, i.e. how do we uniquely group the records with the ones that were earlier tagged. If this is figured out, then rest of the code is a breeze.

In a real-world situation, the TYPE values can be multiple/varying.

Any guidance is much appreciated.

Thank you.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11719
Topics: 74
Location: San Jose

PostPosted: Wed May 31, 2017 10:38 am    Post subject: Re: Conditional data selection Reply with quote

Aki88 wrote:
Hello,

This post is taken from another forum.
It poses an interesting challenge; posting here to expand the DFSORT expert solution base.

Requirement: If first 5 bytes (1,5) of Type is 30000, then for the corresponding code all types should be written to the output file.


Aki88,

Unless I am mistaken, it is a quite simple task. Use the trick of matching the file to it self but for the JNF2 subtask we will only pull the 30000 records. something like this

Code:

//STEP0100 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//INA      DD *                       
10000      12345                     
11000      12345                     
30000      12345                     
20000      12345                     
11000      45624                     
25000      45624                     
34000      33669                     
30000      33669                     
//INB      DD *                       
10000      12345                     
11000      12345                     
30000      12345                     
20000      12345                     
11000      45624                     
25000      45624                     
34000      33669                     
30000      33669                     
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                       
  OPTION COPY                         
  JOINKEYS F1=INA,FIELDS=(12,5,A)     
  JOINKEYS F2=INB,FIELDS=(01,5,A)     
  REFORMAT FIELDS=(F1:1,80)           
//*                                   
//JNF2CNTL DD *                       
  INCLUDE COND=(1,5,CH,EQ,C'30000')   
  INREC BUILD=(12,5)                 
//*


The output from this job is

Code:

10000      12345
11000      12345
30000      12345
20000      12345
34000      33669
30000      33669

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Aki88
Beginner


Joined: 26 Oct 2016
Posts: 8
Topics: 3

PostPosted: Wed May 31, 2017 11:48 am    Post subject: Reply with quote

Hello Kolusu,

As always, hats off to the simplicity of your solution.

I won't lie, EVERYTHING other than JOINKEYS had crossed my mind bonk bonk
And I think it might be because I was working on multiple things that used IFTHEN constructs so JOINKEYS didn't even strike me once, not even close.

Thank you.
Back to top
View user's profile Send private message
Aki88
Beginner


Joined: 26 Oct 2016
Posts: 8
Topics: 3

PostPosted: Tue Jun 13, 2017 1:48 am    Post subject: Reply with quote

Hello Kolusu,

In continuation to our discussion, this is as far I could get (have removed the header values and kept only relevant data):

Code:

//SORTIN   DD *
10000      12345
11000      12345
30000      12345
20000      12345
11000      45624
25000      45624
34000      33669
30000      33669
/*             

--
--

//SYSIN    DD *                                             
 INREC IFTHEN=(WHEN=(1,5,CH,EQ,C'30000'),OVERLAY=(18:C'1')),
       IFTHEN=(WHEN=(1,5,CH,NE,C'30000'),OVERLAY=(18:C'9'))
 SORT FIELDS=(12,7,CH,A)                                   
 OUTFIL IFTHEN=(WHEN=GROUP,KEYBEGIN=(12,5),PUSH=(21:1,5))   
*       IFTHEN=(WHEN=(21,5,CH,EQ,C'30000'),                 
*               BUILD=(1,16,80:X))                         
/*                                                                                                           


This yields:

Code:

30000      12345 1  30000
10000      12345 9  30000
11000      12345 9  30000
20000      12345 9  30000
30000      33669 1  30000
34000      33669 9  30000
11000      45624 9  11000
25000      45624 9  11000


The premise being, add an identifier using the primary key values such that secondary values - if SORTed - become the first character in 'Their' particular GROUP chain.
Then SORT them such that they are GROUPed together.
Now that this is achieved, then it is another GROUP operation with PUSH to uniquely identify them all.
But this is where I lose control over it! Unsure how to drop the remaining records. To my best guess, I think another IFTHEN operation is required in INREC so that I can use an OMIT in OUTFIL.
After which I can work on retaining the original order of records.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 11719
Topics: 74
Location: San Jose

PostPosted: Tue Jun 13, 2017 10:59 am    Post subject: Reply with quote

Aki88,

You are complicating the request too much. It is actually quite simple. Here is a version to give you un-ordered records. ie. 30000 records becomes the first record.

Code:

//***************************************************************
//* USE ALTSEQ CODE TO TRANSLATE '30000' TO SPACES SO WHEN     **
//* SORTED IT WILL BE THE FIRST RECORD.                        **
//*                                                            **
//* '0' = X'F0' | '3'  = X'F3' | SPACE = X'40'                 **
//*                                                            **
//* REPLACING X'F3' AND X'F0' WITH X'40' WILL MAKE             **
//* '30000' AS '     '                                         **
//***************************************************************
//*                                                             
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//SORTIN   DD *                                                 
30000      00000   <<< FIRST RECORD                             
           *****                                                 
10000      11111                                                 
11000      11111                                                 
20000      11111                                                 
30000      11111   <<< LAST RECORD                               
AAAAA      *****                                                 
10000      12345                                                 
11000      12345                                                 
30000      12345   <<< 3RD RECORD                               
20000      12345                                                 
BBBBB      *****                                                 
11000      45624                                                 
25000      45624                                                 
CCCCC      *****                                                 
34000      33669                                                 
30000      33669   <<< LAST RECORD                               
//SORTOUT  DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  ALTSEQ CODE=(F340,F040)                                 
                                                           
  SORT FIELDS=(12,5,CH,A,                                 
               01,5,AQ,A)                                 
                                                           
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN(12,5),PUSH=(81:1,5)) 
  OUTFIL INCLUDE=(81,5,CH,EQ,C'30000'),                   
         BUILD=(1,80)                                     
//*


The output from this job is
Code:

30000      00000   <<< FIRST RECORD   
30000      11111   <<< LAST RECORD     
10000      11111                       
11000      11111                       
20000      11111                       
30000      12345   <<< 3RD RECORD     
10000      12345                       
11000      12345                       
20000      12345                       
30000      33669   <<< LAST RECORD     
34000      33669                       


If you want the records in the order they came in then you need to use the following control cards
Code:

//SYSIN    DD *                                             
  INREC OVERLAY=(81:SEQNUM,3,ZD,RESTART=(12,5))             
                                                           
  ALTSEQ CODE=(F340,F040)                                   
                                                           
  SORT FIELDS=(12,5,CH,A,                                   
               01,5,AQ,A),EQUALS                           
                                                           
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN(12,5),PUSH=(85:1,83)),
         IFTHEN=(WHEN=INIT,                                 
        OVERLAY=(81:SEQNUM,3,ZD,RESTART=(12,5))),           
         IFTHEN=(WHEN=(81,9,CH,EQ,C'001 30000',AND,         
                       81,3,CH,NE,165,3,CH),               
        OVERLAY=(85:5X))                                   
                                                           
  OUTFIL INCLUDE=(85,5,CH,EQ,C'30000'),                     
  IFOUTLEN=80,                                             
  IFTHEN=(WHEN=((81,3,CH,EQ,165,3,CH),AND,81,3,ZD,GT,1),   
  BUILD=(01,80,/85,80))                                     
//*


The output of this would be
Code:

30000      00000   <<< FIRST RECORD 
10000      11111                     
11000      11111                     
20000      11111                     
30000      11111   <<< LAST RECORD   
10000      12345                     
11000      12345                     
30000      12345   <<< 3RD RECORD   
20000      12345                     
34000      33669                     
30000      33669   <<< LAST RECORD   

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Aki88
Beginner


Joined: 26 Oct 2016
Posts: 8
Topics: 3

PostPosted: Thu Jun 15, 2017 12:52 am    Post subject: Reply with quote

Hello Kolusu,

ALTSEQ was a nifty one! I agree, it saves the whole shebang of configuring a way to bring the record on top.

Aside, on looking at your SORT card, I realized I was missing an OUTREC Sad ; if I had to go by my original thought, then this is how the card looks now (have retained the extra bytes of data for testing):

Code:


 INREC IFTHEN=(WHEN=(1,5,CH,EQ,C'30000'),OVERLAY=(18:C'1')),
        IFTHEN=(WHEN=(1,5,CH,NE,C'30000'),OVERLAY=(18:C'9'))
 SORT FIELDS=(12,7,CH,A)                                   
 OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(12,5),PUSH=(21:1,5))   
 OUTFIL INCLUDE=(21,5,CH,EQ,C'30000')                       


This yields:

Code:

30000      12345 1  30000
10000      12345 9  30000
11000      12345 9  30000
20000      12345 9  30000
30000      33669 1  30000
34000      33669 9  30000



Thank you Kolusu for your time on this one, it was darn cool working out various methods to find the end trick. Very Happy
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities 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