Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Duplicate Keys Sorting with IF conditions on Multiple Column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Mon Mar 12, 2012 7:31 pm    Post subject: Duplicate Keys Sorting with IF conditions on Multiple Column
Reply with quote

Column 1 - 4 is the Key (account number). That differentiates each record. Therefore Column 1 -4 will be called the Key Number in this requirement.

Requirement:
------------
1. For a given KEY NUMBER when column position 6 - 10 is 00000, Check if Column 16 - 17 is spaces.

2. If above is TRUE then check for a record with the same KEY NUMBER but column position 6 - 10 is 11111, and Column position 22 - 23 is 'ZZ' or 'YY' or 'WW'.

3. If Column position 22 - 23 for the row as stated in pt. 2 above is 'ZZ' or 'YY' or 'WW', write all the Rows with that KEY NUMBER to a FILE called FILE-A

4. If Column position 22 - 23 for the row as stated in pt. 2 above is NOT EQUAL to 'ZZ' or 'YY' or 'WW', write all the rows with that KEY NUMBER to a FILE called FILE-B


INPUT DATA:
=========

Code:
=COLS>    ----+----1----+----2----+
******    *************************
000001    1111 00000 CCC 99XXX     
000002    2222 00000 CCC   XXX     
000003    2222 11111 CCC 77XXX ZZ 
000004    3333 00000 CCC 99XXX     
000005    4444 00000 CCC 99XXX     
000006    5555 00000 OOO   XXX     
000007    5555 11111 DDD 99XXX AA 
000008    6666 00000 MMM 99XXX     
******    *************************



OUTPUT DATA:
==========

FILE - A
----------

Code:
=COLS>    ----+----1----+----2----+
******    *************************
000002    2222 00000 CCC   XXX     
000003    2222 11111 CCC 77XXX ZZ 
******    *************************


FILE - B
---------

Code:
=COLS>    ----+----1----+----2----+
******    *************************
000006    5555 00000 OOO   XXX     
000007    5555 11111 DDD 99XXX AA 
******    *************************
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Mar 12, 2012 8:46 pm    Post subject:
Reply with quote

mi.aarthi,
Please answer below questions.

1) What is the RECFM and LRECL for the input/output file(s)?

2) Can there be more than 2 records for any account number? If your point#1 condition is true, you want to look for another record(s) with the same account number? Can there be only 1 other record with the same account number or multiple?

3) I could be wrong but position 16,17 has values in all the sample records you provided.

Thanks,
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Mon Mar 12, 2012 8:52 pm    Post subject:
Reply with quote

Dear Forum Members,

A part of my header message is missing from the post.
Well I am typing it here. The above is my requirement. My approach to handle this situation is by the following steps

STEP 1:
SORT and create a file using the Input file based on those KEY NUMBERS that have duplicates.

STEP 2:
Make 2 copies of the file (InpFile 1 & InpFile 2) and use ICETOOL SPLICE to check for a KEY NUMBER in InpFile 1 that has '00000' in position 6 - 10 and SPACES in poistion in Column 16 & 17 has same KEY NUMBER record in InpFile 2 that has '11111' in position 6 - 10 and 'zz' or 'yy' or 'ww' in position 22 & 23.

Don't know how to proceed further. Please advice me or if the same type of thread has been solved in the past then please point me to that.

Thanks
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Mon Mar 12, 2012 8:57 pm    Post subject:
Reply with quote

mi.aarthi,
If you answer all the questions previously asked you may not need all these.

Also, disregard question #3.

Thanks,
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Mon Mar 12, 2012 8:57 pm    Post subject:
Reply with quote

sqlcode1 wrote:
mi.aarthi,
Please answer below questions.

1) What is the RECFM and LRECL for the input/output file(s)?

2) Can there be more than 2 records for any account number? If your point#1 condition is true, you want to look for another record(s) with the same account number? Can there be only 1 other record with the same account number or multiple?

Thanks,


Thanks for your reply SQLCODE1.

1. RECFM = VB (But for the solution lets understand that the file is FB)
LRECL = 100

2. Yes there can be more than 2 records for any account Number.
Also there are chances of only 1 other record with the same Account
number or Multiple.

I am more than happy to answer any queries you have on the requirement. Hence please let me know in case you need more details.

Cheers
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Mon Mar 12, 2012 8:59 pm    Post subject:
Reply with quote

sqlcode1 wrote:
mi.aarthi,
If you answer all the questions previously asked you may not need all these.

Also, disregard question #3.

Thanks,


Also to answer Question 3. - Probably the post that I have copied and pasted has moved records from left to position 16 & 17.

But are you able to view the attached .JPG file (picture of the mainframe screenshot).

Cheers
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Mon Mar 12, 2012 9:01 pm    Post subject: Reply to: Duplicate Keys Sorting with IF conditions on Multi
Reply with quote

And use the Code tags for your sample data. Then we'd know without checking whether 16-17 has space in your sample.

Please don't post screenshots. They are valueless (we are not going to re-type anything from an image) whereas if you paste and use the code tags, we can copy and paste your data.
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Mon Mar 12, 2012 9:02 pm    Post subject: Re: Reply to: Duplicate Keys Sorting with IF conditions on M
Reply with quote

Bill Woodger wrote:
And use the Code tags for your sample data. Then we'd know without checking whether 16-17 has space in your sample.

Please don't post screenshots. They are valueless (we are not going to re-type anything from an image) whereas if you paste and use the code tags, we can copy and paste your data.


Thanks Bill. I have never used it before, but will start using it.
Cheers icon_smile.gif
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Mar 13, 2012 12:11 am    Post subject:
Reply with quote

mi.aarthi,

Quote:
1. RECFM = VB (But for the solution lets understand that the file is FB)
LRECL = 100
No it doesn't work like that. FB or VB changes the way temp. data is populated.


If you don't care about the order of records in the final file, see if below works for you... FYI... Apart from other assumptions, I assumed that you will have 00000 and 11111 values in their respective positions only. If that's not correct, please explain the rules,along with sample input/output records, for other values of position 6-10 from your original post.

TESTED WITH VB104.
Code:
//STEP0001     EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DISP=SHR,DSN=INPUT VB                                   
//SORTJNF2 DD DISP=SHR,DSN=INPUT VB                                   
//OUT1     DD  DSN=OUTPUT VB1                                         
//OUT2     DD  DSN=OUTPUT VB1                                         
//SYSIN    DD *                                                       
  OPTION VLSCMP                                                       
  JOINKEYS FILES=F1,FIELDS=(05,04,A)                                   
  JOINKEYS FILES=F2,FIELDS=(05,04,A)                                   
  REFORMAT FIELDS=(F1:1,4,F1:5)                                       
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,7X,5))                           
  SORT FIELDS=(12,4,ZD,A,17,5,ZD,D),EQUALS                             
  OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(12,4),PUSH=(5:17,5,10:33,2))     
  OUTFIL FNAMES=OUT1,INCLUDE=(05,5,ZD,EQ,11111,AND,                   
                             (10,2,SS,EQ,C'ZZ,YY,WW')),BUILD=(1,4,12) 
  OUTFIL FNAMES=OUT2,INCLUDE=(05,5,ZD,EQ,11111,AND,                   
                             (10,2,SS,NE,C'ZZ,YY,WW')),BUILD=(1,4,12) 
/*                                                                     
//JNF2CNTL DD *                                                       
 INCLUDE COND=(10,5,ZD,EQ,00000,AND,20,2,CH,EQ,C' ')                   
 SUM FIELDS=NONE                                                       
//*                                                                   


OUT FILE1 with ZZ,YY,WW
Code:

2222 11111 CCC 77XXX ZZ
2222 00000 CCC   XXX   

OUT FILE2 with others
Code:

5555 11111 DDD 99XXX AA
5555 00000 OOO   XXX   

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Mar 13, 2012 12:27 am    Post subject:
Reply with quote

mi.aarthi,

You could probably do this with a DFSORT JOINKEYS job, but when I started to work it out, I realized that your "rules" are too vague and your example does NOT cover all cases. If you can provide a better description of your rules and an example of input and output that covers all cases, I might be able to show you how to do what you want with JOINKEYS.

Quote:
1. RECFM = VB (But for the solution lets understand that the file is FB) LRECL = 100


If we do that, you'll then come back and ask how to do it for VB. So let's do it for VB in the first place. Give the rules, positions, example, etc for VB.
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Tue Mar 13, 2012 5:58 am    Post subject:
Reply with quote

Dear sqlcode1,

Thank you for your valuable time and effort to provide a solution. I am currently trying to use the SORT program that you have provided in order to achieve my requirement. However I am trying to understand every lineof code that you have provided in your latest post. Understanding this would be of great value for resolving various future file manipulation issues.

I am new to JOINKEYS and hence reading the 5th chapter in DFSORT Application programming Guide.
What does a REFORMAT and PUSH Command do in DFSORT JOINKEYS.
Back to top
View user's profile Send private message
mi.aarthi

New User


Joined: 14 May 2008
Posts: 12
Location: Milton Keynes

PostPosted: Tue Mar 13, 2012 6:07 am    Post subject:
Reply with quote

Frank Yaeger wrote:
mi.aarthi,

You could probably do this with a DFSORT JOINKEYS job, but when I started to work it out, I realized that your "rules" are too vague and your example does NOT cover all cases. If you can provide a better description of your rules and an example of input and output that covers all cases, I might be able to show you how to do what you want with JOINKEYS.

Quote:
1. RECFM = VB (But for the solution lets understand that the file is FB) LRECL = 100


If we do that, you'll then come back and ask how to do it for VB. So let's do it for VB in the first place. Give the rules, positions, example, etc for VB.


Hello Frank, Thank you for reading the post and instructing on it.

1. Yes this is a VB File input and the maximum length of the input file that I see is 690 bytes.

2. Apart from this above detail I believe that I have no changes in the RULES and there isn't any requirement that I missed.


At the moment I am tryinng to use sqlcode1's advice. However I will be more than happy if you have another solution using JOINKEYS.

I am trying to understand each line in the SORT in order to understand the concept...

Please support me in this case
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue Mar 13, 2012 7:02 am    Post subject:
Reply with quote

mi.aarthi,

mi.aarthi wrote:

What does a REFORMAT and PUSH Command do in DFSORT JOINKEYS.


Read about Reformat and PUSH

Thanks,
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Mar 13, 2012 9:42 pm    Post subject: Reply to: Duplicate Keys Sorting with IF conditions on Multi
Reply with quote

Sqlcode1,

You don't need a SORT after the join to get the desired results. You can retain the order as is and use a COPY instead of SORT

mi.aarthi,

Use the following DFSORT JCL which will give you the desired results. I assumed that you have 1 record which has '11111' record per key in pos 10 ( VB file pos 6 translates to pos 10). If you have multiple '11111' records per key then add SUM FIELDS=NONE in the JNF2CNTL statement.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD DSN=Your input VB file,DISP=SHR
//INB      DD DSN=Same input VB file again,DISP=SHR         
//OUT1     DD SYSOUT=*                                             
//OUT2     DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(5,4,A)                                   
  JOINKEYS F2=INB,FIELDS=(5,4,A)                                   
  REFORMAT FIELDS=(F1:1,4,?,F2:30,3,10,5,F1:5)                     
  INREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(14,4),PUSH=(7:29,2,19,5))     
                                                                   
  OUTFIL FNAMES=OUT1,BUILD=(1,4,14),INCLUDE=(5,9,CH,EQ,C'BA  00000')
  OUTFIL FNAMES=OUT2,BUILD=(1,4,14),INCLUDE=(5,9,CH,EQ,C'BB  00000')
//*
//JNF2CNTL DD *                                                     
  OPTION VLSCMP                                                     
  INCLUDE COND=(10,5,CH,EQ,C'11111',AND,26,2,CH,GT,C' ')           
  INREC IFTHEN=(WHEN=(26,2,SS,EQ,C'ZZ,YY,WW'),OVERLAY=(30:C'AAA')),
  IFTHEN=(WHEN=NONE,OVERLAY=(30:C'BBB'))   
//*
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us