|
View previous topic :: View next topic
|
| Author |
Message |
venkatatcts
New User
Joined: 16 Mar 2009
Posts: 55
Location: India
|
|
|
|
Hi,
I have a file which as multiple records for the same key but with a field which tells whether the transaction is deleted or added or changed.
For example,
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
Here D - deleted, A - Add and C - Change
If any one records has D then the key should be removed. But if there record does not have D then all the record should be writted to file. The output should be
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
Thanks,
Venkat B |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| Very simple - what have you tried? I will give you a clue - you want to omit some records. |
|
| Back to top |
|
|
venkatatcts
New User
Joined: 16 Mar 2009
Posts: 55
Location: India
|
|
|
|
The file is sorted based on key but the field which tells whether it is deleted or changed or added is sorted in desc order.
So if there is any record which is deleted for a key it will be first one. So that key should not be there. For any record all the three are possible Delete, Add, Change.
Omit will not work for the first sceanrio.
Thanks,
Venkat |
|
| Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
Hello,
Is the set occurance of records would be 3 always?
| Quote: |
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A |
|
|
| Back to top |
|
|
venkatatcts
New User
Joined: 16 Mar 2009
Posts: 55
Location: India
|
|
|
|
| No it can be 1 or 2 or maxmium of 999 occurence for a single key |
|
| Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
Hello,
Try the below snippet,
| Code: |
//STEP1 EXEC PGM=ICETOOL
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//IN1 DD *
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
//OUT1 DD DSN=&TMP1,
// DISP=(NEW,CATLG,DELETE),
// RECFM=FB,LRECL=90,BLKSIZE=0,
// SPACE=(CYL,(10,10),RLSE),UNIT=SYSDA
//OUT2 DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN1) TO(OUT1) USING(CTL1)
/*
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=(10,1,CH,EQ,C'D'),OVERLAY=(81:1,8))
OUTREC FIELDS=(1:81,8)
/*
//STEP02 EXEC PGM=SORT
//SORTJNF1 DD DSN=&TMP1,DISP=SHR
//SORTJNF2 DD *
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,8,A)
JOINKEYS FILE=F2,FIELDS=(1,8,A)
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F2:1,80)
SORT FIELDS=COPY |
More one than one step has been used since the occurance is unknown. You can reduce it to single step if you have clear information. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Which release of which sort product is being used? |
|
| Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006
Posts: 82
Location: UK
|
|
|
|
First thing first. You have not answered Nic's question. You have also not searched the forum and the like. Are you waiting for a custom made solution?
Hint #2 - This can be achieved in one step. Use WHEN=GROUP on the key if you are using DFSORT. |
|
| Back to top |
|
|
venkatatcts
New User
Joined: 16 Mar 2009
Posts: 55
Location: India
|
|
|
|
Hi,
I am using syncsort 1.3
I cannot achieve this by omit condition. Based on the column which tell whether it is delete or add or change I have to decide whether the key need to be deleted.
Thanks,
Venkat b |
|
| Back to top |
|
|
xknight
Active User
Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
|
|
|
|
Hello,
| Quote: |
| I am using syncsort 1.3 |
So did you tried or  |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi,
I agree with Naish, when GROUP is a better option and only requires 1 pass of the file.
Gerry |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
The examples are unclear.
Why would a "D" come first? Can a "D" come anywhere else in the sequence of records for a key? Why does "C" come before "A"?
Without KEYBEGIN the GROUP is more tricky.
Does Syncsort have OMIT/INCLUDE available on JOINKEYS? |
|
| Back to top |
|
|
venkatatcts
New User
Joined: 16 Mar 2009
Posts: 55
Location: India
|
|
|
|
I have sorted the file by desc so that D comes first and then C and then A.
Sycnsort has include and omit conditon.
Thanks,
Venkat |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
You need to use something like this
| Code: |
JOINKEYS FILES=F1,FIELDS=(1,8,A),INCLUDE=(10,1,CH,EQ,C'D')
JOINKEYS FILES=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F1:1,10,F2:1,10),FILL=X'FF'
JOIN UNPAIRED F1,F2
SORT FIELDS=COPY
OMIT COND=(1,10,CH,EQ,11,10,CH)
|
UNTESTED |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Pandora-Box,
You'd just need the "unmatched" records from the main file, no extra messing, so UNPAIRED,F2,ONLY.
Any matches must be for "deleteds". Same source file, so no possible "extras" on the deleteds file. the JOINKEYS gives the records wanted (all, without any which have D).
If the data is already sorted, that can be indicated. With this solution the "D"s are not required to be first, just to be present. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Ah Agreed Bill  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I am using syncsort 1.3 |
Is there more to the release shown (i.e. 1.3.x)?
My 1.3 doc shows when=group, but i don't recall if there was a sublevel of th release that is required.
1.3 is rather outdated now and i'd suggest your orgainzation consider the upgrade to the current release. |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi,
here is a way using when=group
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
XX1-A-DW D
XX1-A-DW C
XX1-A-DW A
XX1-B-DW C
XX1-B-DW A
XX1-B-DW A
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,8))),
IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,+1),
PUSH=(89:10,1))
OUTFIL OMIT=(89,1,CH,EQ,C'D'),BUILD=(1,80)
//*
|
Gerry |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Venkat B,
You now have a couple of solutions.
If the only purpose of the descending sort on the A/C/D is for this task, then the JOINKEYS approach outlined by xnight and Pandora-Box can remove the need for that sort, as it does not matter where the D occurs as only the D is extracted for that file.
If you have some other need of the file in that descending order, then Gerry's solution should run better than the JOINKEYS.
If you go with the JOINKEYS and your file is in its "natural" order, then be aware that you can stop the sort on each of the JOINKEYS files by adding SORTED. |
|
| Back to top |
|
|
|
|
