|
View previous topic :: View next topic
|
| Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
I have two files (LRECL=16,RECFM=FB) and requirement is to get unpaired records from file-1 (detail records) and matched records also from file-1 (header and trailer).
File-1:
| Code: |
HEADER 00000136
XXXXA tb9 CUPA
XXXXA S1T SCAL
XXXXA F8C WHOM
TRAILER
|
File-2:
| Code: |
HEADER 00000136
XXXXA D9J CYCL
XXXXA O6C CLER
XXXXA tb9 CUPA
TRAILER
|
I wrote below sort card -
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,16,A)
JOINKEYS FILE=F2,FIELDS=(1,16,A)
JOIN UNPAIRED,F1,ONLY
SORT FIELDS=COPY
END
|
and got two reords from file-1
| Code: |
XXXXA S1T SCAL
XXXXA F8C WHOM
|
but my desired output should appear as given below -
| Code: |
HEADER 00000136
XXXXA S1T SCAL
XXXXA F8C WHOM
TRAILER
|
How to get this? Please help. |
|
| Back to top |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Some work, that's how :-)
You have "groups" of data, which have "keys within a group"?
Are the the keys witing a group both unique across your data and solely contiguous within the group. Ie, when you SORT on 1,16, are any records from on group going to be mixed in with another. Is it possible?
Are the HEADERs for the groups on the two files always identical for a group?
Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or what? |
|
| Back to top |
|
|
mistah kurtz
Active User
Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
|
|
|
|
| Quote: |
| to get unpaired records from file-1 (detail records) and matched records also from file-1 (header and trailer). |
I'm sorry, but I didn't fully understand this part. Do you mean you need all the unpaired records from File1 with Header and Trailer. What if File1 and File2 have some matching records apart from Header and Trailer? |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
What you could do is something like below untested though
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,16,A)
JOINKEYS FILE=F2,FIELDS=(1,16,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,16,F2:1,16)
INCLUDE COND=((1,16,CH,EQ,17,16,CH,AND,(1,7,CH,EQ,C'HEADER ',
OR,1,7,CH,EQ,C'TRAILER')),OR,(17,16,CH,EQ,C' '))
END |
|
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
| Pandora-box, thank you.....I got expected results. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Ramsri,
Would you mind sharing the solution? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Ramsri, as with your previous topic, I'm surprised you've got the results you want other than with the very limited data you've shown.
We're getting very good at solving your problems, we just have no clue as to how we do it :-) |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Bill Woodger wrote: |
| We're getting very good at solving your problems, we just have no clue as to how we do it :-) |
 I thought the same Bill |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
| We're getting very good at solving your problems, we just have no clue as to how we do it :-) |
But frankly I was able to decrypt what he wanted
Edit : And yes I was expecting TS to tweek my post slightly  |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Pandora-Box wrote: |
But frankly I was able to decrypt what he wanted
Edit : And yes I was expecting TS to tweek my post slightly |
Pandora,
We can very well provide solutions to the example data provided, but most of the time, it may not be realistic. Say for example, the HEADER/TRAILER records may not exactly be the same in the real input files. So the solution provided should be based on realistic data capable of handling all input scenarios. That is the whole idea behind asking questions and nothing was answered here. Any solution provided without knowing the characteristics of real data would be solely based on assumptions and cannot be expected to work as intended. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Arun,
I was mentioning about "tweek" for considering the files as SORTED.
Anyway apologies for providing a half-baked solution  |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Arun/Bill, I must admit that I changed starting letter to be O instead of X when tried it in reality.... Otherwise, I was ending up getting in wrong results 
 I added
| Code: |
| OUTREC FIELDS=(1,16) |
to Pandora's solution.
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| ramsri wrote: |
| I changed starting letter to be O instead of X when tried it in reality |
I have no clue on what this is about. Anyways does n't matter as you have got your card working!
I would say a JOIN UNPAIRED,F1 would have been sufficient.
Or possibly even a JOIN UNPAIRED,F1,ONLY, but I can only guess with the limited info provided so far. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Quote: |
I would say a JOIN UNPAIRED,F1 would have been sufficient.
Or possibly even a JOIN UNPAIRED,F1,ONLY, but I can only guess with the limited info provided so far. |
Just curious How do you get the header and Trailer? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Pandora,
| Code: |
| INCLUDE COND=(1,7,SS,EQ,C'HEADER ,TRAILER',OR,..... |
This will extract the header and trailer records into output if you are doing a JOIN,UNPAIRED,F1. But you need to add logic to retain the order of header and trailer records. The 'solution' provided does not handle this.
Or if the header and trailer records have static data, we can have a JOIN UNPAIRED,F1,ONLY and hard-code the header and trailer records using OUTFIL HEADER1/TRAILER1.
But with the OP ignoring all the questions/details requested, one can only guess. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Yes Arun. As this shows:
| Quote: |
| I must admit that I changed starting letter to be O instead of X when tried it in reality... |
Only by changuing the first X in the sample data to an O did the solution "work", as otherwise the TRAILER would appear before the data.
Ramsri, you will never get that to work with more than one group of data, or unless all you data starts with characters less than the trailer does.
We haven't even got to ask you if you want to retain the order of data within the group. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Ok I get that Arun
Thanks for the clarification |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Bill/Arun, there are no multiple groups within this file. There is always one header and trailer with detail records in between. I wonder if it is still achievable if the starting letter is some B or P.
Thanks. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
What happened when you tried?
If you have not tried, do so and post the results if there is a question/problem. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Bill Woodger wrote: |
Are the HEADERs for the groups on the two files always identical for a group?
Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or what? |
Ramsri,
Do you find it difficult to answer to all of these? |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Arun,
| Quote: |
Are the HEADERs for the groups on the two files always identical for a group?
|
The file will have only one HEADER and yes.....they are identical.
| Quote: |
Do the TRAILERs simply contain the word TRAILER, or some other "constant" value, or what?
|
Yes......it is just TRAILER and nothing.
Thanks. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
To Repeat:
| Quote: |
What happened when you tried?
If you have not tried, do so and post the results if there is a question/problem. |
If you are unwilling to do things suggested and reply as to what happened, we should start locking your topics. . . |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Ramsri,
I've lost track of whether you can use JNFnCNTL files.
If not, use OMIT on the JOINKEYS for your F2 to get rid of the header and trailer.
They will then "pop up" on your F1,ONLY output. In the wrong order.
You'll have to SORT them. Append an X'00' to the header, X'FF' to the trailer and C'1' to each data record. SORT on with, with EQUALS.
Or make a sequence number, set to zero for the header, and all 9 for the trailer. SORT on that, with NOEQUALS.
Don't forget to cut the record back down afterwards (OUTREC BUILD=).
If you can use JNF1CNTL, then the processing to order the header and trailer can be done in there and the cut-down in the REFORMAT. |
|
| Back to top |
|
|
|
|
