Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Yes, if you have a file containing a list of shop-numbers and an indicator of which file they are to go to, it would be a simple JOINKEYS on the shop-number.
The REFORMAT statement would contain the entire F1 record plus the indicator from F2. Three OUTFIL statements (or four, you need to consider where you want "typos" to go, depending on how you create the driver file). Use SAVE for one of the OUTFILs, and BUILD on each to cut off the indicator value.
No JOIN statement needed, so you only get the matches.
Do you expect to have gaps and/or overlap in the shop number? Can your data be like below? In the first example below, we have a gap between record 3rd and 4th and in the second example, 2449 is on the first record as well as on the 4th one.
I don't understand it. What is the output you are looking for?
In your first post, it looked like there could only be one shop number in a row. But in your recent post it seems there will be 3 shop numbers in a single row belonging to shop1, 2 and 3.
Now, if all the shops numbers are present in all the rows, how do you want to split them? Do you want shops number 1 in one file, shop number 2 in file two, etc. ?? (which is really simple to do)
Do you expect to have gaps and/or overlap in the shop number? Can your data be like below? In the first example below, we have a gap between record 3rd and 4th and in the second example, 2449 is on the first record as well as on the 4th one.
I don't understand it. What is the output you are looking for?
In your first post, it looked like there could only be one shop number in a row. But in your recent post it seems there will be 3 shop numbers in a single row belonging to shop1, 2 and 3.
Now, if all the shops numbers are present in all the rows, how do you want to split them? Do you want shops number 1 in one file, shop number 2 in file two, etc. ?? (which is really simple to do)
Can you show expected output ?
Hi Rahul,
Sorry for not being specific
there are two input files
File#1 which has the shop numbers as below
sun_job,
If you don't care about writting records when there is no match its relatively simple solution (first one below) but if you want orphan records to go some file for reporting purpose use second one.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
You really must learn how to post your query properly: this is not a JCL or VSAM topic but a sort topic so it should have been posted in the appropriate part of the forum for your sort product. Also, you would be asking help with the sort control cards not JCL.
When posting sort queries like yours you should specify representative sample data and the expected output from that data and the rules for getting that output from the input.
Representative data should show duplicates, gaps, different record types etc should they exist.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
sqlcode1's solution is a full join, meaning every record on F2 is matched to every record on F1 (through the 'X' added at position 81).
With large amounts of data, there is a performance impact.
With 1000 data records and four "shop code" records, 4000 REFORMAT records will be created.
With 100,000 data records and 50 "shop code" records, 5,000,000 REFORMAT records will be created.
Then all the records will be SORTed.
The larger file is better as F1.
RahulG31's second solution, which I would do as two SORT steps rather than use ICETOOL just to have one step, will likely be more efficient. The data file looks to be in order, so SORTED,NOSEQCK for that.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
If you don't get the results you want, please describe your problem better, with representative sample data, and an idea of the volumes you have to deal with.
You really must learn how to post your query properly: this is not a JCL or VSAM topic but a sort topic so it should have been posted in the appropriate part of the forum for your sort product. Also, you would be asking help with the sort control cards not JCL.
When posting sort queries like yours you should specify representative sample data and the expected output from that data and the rules for getting that output from the input.
Representative data should show duplicates, gaps, different record types etc should they exist.