|
View previous topic :: View next topic
|
| Author |
Message |
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
Hi,
I would like to achieve the following result using Syncsort. I can use Ezytrieve but I would like to know if it is possible using Syncsort.
Input File 01
Account (Key), Status, Effective Date, End Date
Input File 02
Account (Key), Status, Effective Date, End Date
Output File
Account, Status, Effective Date, End Date
The Status (single byte) can be anything from A - Z.
Input File 01 and 02 can have identical number of records or it is possible that File 01 records > File 02 records.
I will write the Input File 01 to the output except that when Input File 01 has a Status of 'P', I will use the End Date of Input File 02 and replace the End Date of File 01 and write it to the output. Also, please show if it can be done on a single step.
For ex.
Input File 01
A123456789X10/21/200911/21/2009
A987654321P10/21/200911/21/2009
Input File 02
A123456789X10/21/200911/21/2009
A987654321P11/21/200912/21/2009
Output
A123456789X10/21/200911/21/2009
A987654321P10/21/200912/21/2009
I tried to search on something similar like this and end up seeing mostly JOIN statements..but I don't have any guidelines on how to use it..mostly I rely on examples but examples don't explain everything.
Thank you in advance. |
|
| Back to top |
|
 |
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007
Posts: 496
Location: USA
|
|
|
|
Here is a SyncSort for z/OS job that will provide you with the requested output:
| Code: |
//STEP1 EXEC PGM=SORT
//SORTJNF1 DD *
A123456789X10/21/200911/21/2009
A987654321P10/21/200911/21/2009
//SORTJNF2 DD *
A123456789X10/21/200911/21/2009
A987654321P11/21/200912/21/2009
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,31,F2:22,10)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(11,1,CH,EQ,C'P'),BUILD=(1,21,32,10)),
IFTHEN=(WHEN=NONE,BUILD=(1,31))
/* |
|
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
thanks for that Alissa..you always answer questions promptly on syncsort problems.
can you explain the JOIN and the other statements used on the sysin as I can only understand a bit of it
here is how I understand it
JOINKEYS FILES=F1,FIELDS=(1,10,A)
JOINKEYS FILES=F2,FIELDS=(1,10,A)
*accounts were used as keys
JOIN UNPAIRED,F1
** no idea
REFORMAT FIELDS=(F1:1,31,F2:22,10)
*this one is a copy of F1 file plus the End date of F2?
OUTREC IFTHEN=(WHEN=(11,1,CH,EQ,C'P'),BUILD=(1,21,32,10)),
*since you reformatted the record F1 + F2 End date, when the condition is met you put the F2 End date on the position of F1 End date
IFTHEN=(WHEN=NONE,BUILD=(1,31))
*not sure what is WHEN=NONE, but I am assuming that this one just writes the F1 to the output. |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
I tried the code Alissa and it works. Big Thanks!
Now I wish I have a manual on this. It would save me a lot of time on coding similar ezytrieve programs. |
|
| Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table
|
|
|
|
| Michael Angelo Pagala wrote: |
JOIN UNPAIRED,F1
** no idea |
| Exploiting SyncSort JOIN wrote: |
| JOIN - UNPAIRED - Join only paired records |
| Quote: |
IFTHEN=(WHEN=NONE,BUILD=(1,31))
*not sure what is WHEN=NONE, but I am assuming that this one just writes the F1 to the output. |
| SyncSort for z/OS 1.3 Programmer’s Guide wrote: |
WHEN=NONE
The WHEN=NONE subparameter condition is satisfied if none of the preceding WHEN=(conditions) subparameter conditions is satisfied or if there are no WHEN=(conditions) subparameters. If the
WHEN=NONE subparameter condition is satisfied, it applies the specified second subparameter to the temporary record. |
I would suggest you contact Alissa Margulies or the Syncsort support staff to get a current level manual and any additional supporting manuals for the current level of Syncsort you are currently at........ |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
thank you CICS guy..I will try to PM Alissa for that
---
I can't see any PM button here. I hope Alissa will post here again and provide her contact details |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| Have a look at her signature, this is the Id you can use: zos_tech@syncsort.com . |
|
| Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007
Posts: 496
Location: USA
|
|
|
|
Just to clarify... JOIN UNPAIRED,F1 will select the paired records in addition to the unique or unpaired records from the first file. If JOIN UNPAIRED,F1,ONLY were specified, then only the unmatched records from File1 would be selected.
Feel free to contact me at alissa.margulies@syncsort.com and I would be happy to assist you with acquiring the manuals. |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
| thanks for the clarification Alissa.I will also try and ask if they have a copy of the latest syncsort manual here |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you also do as Alissa mentioned:
| Quote: |
| Feel free to contact me at alissa.margulies@syncsort.com and I would be happy to assist you with acquiring the manuals. |
|
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
May I ask another question?
What if I just want to write only the paired records and write that to the output which falls under a certain condition and omit the rest including unpaired and those that did not meet the condition.
So the output will be either empty or have records (paired and met condition).
Key is the 1st byte
Input 1
A1234501/01/2010
B1234502/01/2010
Input 2
A1234502/01/2010
B1234501/01/2010
Condition - Date of Paired Records must be equal and be written to the output.
Expected output
Empty (if the condition is met then this will not be the case)
Since UNPAIRED select both paired and unpaired, is there anyway I can get only the paired that met the condition and omit everything else.
is there a code such as WHEN=NONE,OMIT? :d |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
I think I found a solution to my prob.
I used the INCLUDE COND instead of the IFTHEN..
I filtered the paired by checking the keys if they are spaces in the reformat fields just like with the other examples posted. If there is any other option please post it here. |
|
| Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007
Posts: 496
Location: USA
|
|
|
|
| If you only want the paired records, then you can simply remove the JOIN UNPAIRED,F1 statement altogether. |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
| Alissa Margulies wrote: |
| If you only want the paired records, then you can simply remove the JOIN UNPAIRED,F1 statement altogether. |
so it means that JOINKEYS does not necessarily mean that we need to indicate JOIN UNPAIRED too? so JOINKEYS default selects paired records?
I have requested a copy of the manual here and they gave me a 1.2 version..IFTHEN,BUILD statements were not included in the manual. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If you successfully execute IFTHEN/WHEN/etc, suggest you work with Alissa to download the current documentation. . .
When you run Syncsort, which release is shown in the informational output? |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
| dick scherrer wrote: |
Hello,
If you successfully execute IFTHEN/WHEN/etc, suggest you work with Alissa to download the current documentation. . .
When you run Syncsort, which release is shown in the informational output? |
Here is what I can see on the sysout after I run a syncsort >
SYNCSORT FOR Z/OS 1.3.2.1R
yeah I will try to find a way to email Alissa. I can't use my office email for such.
thanks! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
The good news is that your release supports IFTHEN. . .
If you can get to the forum at work, you can use the PM facility to send Alissa a Private Message. . . |
|
| Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007
Posts: 496
Location: USA
|
|
|
|
Michael,
You can contact me offline at alissa.margulies@syncsort.com for an Addendum to the SyncSort for z/OS 1.2 Programmer's Guide, which contains the documentation for IFTHEN/BUILD/OVERLAY, etc.
Based on your previous post, you are running SyncSort for z/OS 1.3.2.1. The SyncSort for z/OS 1.3 Programmer's Guide will also contain the full documentation for those features.
(If email is an issue, you can send me a private message here in the forum). |
|
| Back to top |
|
|
Michael Angelo Pagala
New User
Joined: 02 Mar 2007
Posts: 34
Location: somewhere in the middle
|
|
|
|
Thank you very much for the info and help!
I already sent a PM to Alissa. |
|
| Back to top |
|
|
|
|
