|
View previous topic :: View next topic
|
| Author |
Message |
rohanthengal
Active User
.jpg)
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
hi team,
i need your help in icetool splice.
INPUT FILE 1:
| Code: |
ACCOUNT1 10000
ACCOUNT2 10001
ACCOUNT3 10002
ACCOUNT4 10003
|
INPUT FILE 2:
| Code: |
10000 ALLEN CUST10000A
10000 ALEX CUST10000B
10001 JISHO CUST10001A
10001 FRANK CUST10001B
10001 KENRE CUST10001C
10002 KEITH CUST10002A
10002 LEMO CUST10002B
10004 KRISH CUST10004A
10004 AMRO CUST10004B
|
here account number(key) e.g. 10001 in input file 1 should be matched with account number in input file 2, and corresponding every matched record from input file 2 should be written to the output file by setting an end flag to Y -- as shown below --
| Code: |
10000 ALLEN CUST10000A Y
10000 ALEX CUST10000B Y
10001 JISHO CUST10001A Y
10001 FRANK CUST10001B Y
10001 KENRE CUST10001C Y
10002 KEITH CUST10002A Y
10002 LEMO CUST10002B Y
|
I have written a icetool splice control card which gives me output:
| Code: |
10000 ALLEN CUST10000A Y
10001 JISHO CUST10001A Y
10002 LEMO CUST10002B Y
|
i am getting only first occurrence from input file 2, under that particular account number !! Please suggest me SPLICE statement with right option |
|
| Back to top |
|
 |
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
rohanthengal,
What is LRECL/RECFM for both the input files? Why not JOINKEYS, is that not supported in currect DFSort function level?
Is your input file1, unique at account number?
Thanks, |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
rohanthengal,
You could do this with SPLICE, but it's much easier with JOINKEYS. You could use a DFSORT JOINKEYS job like the following. I assumed input file2 has RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes:
| Code: |
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB)
//IN2 DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=... output file (FB/80)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(11,5,A),SORTED
JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED
REFORMAT FIELDS=(F2:1,80)
OPTION COPY
INREC OVERLAY=(28:C'Y')
/*
|
|
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
thanks a lot FRrank , its working...
Now i want to include those records from IN2 which did not satisfy the matching criteria...for whom the indicator 'Y' will not be set
so the output will look like this:
10000 ALLEN CUST10000A Y
10000 ALEX CUST10000B Y
10001 JISHO CUST10001A Y
10001 FRANK CUST10001B Y
10001 KENRE CUST10001C Y
10002 KEITH CUST10002A Y
10002 LEMO CUST10002B Y
10004 KRISH CUST10004A
10004 AMRO CUST10004B |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
How many more "requirements" are to be added . . . 
It is rather inconsiderate to ask people to keep providing more answers when you are not willing to get all of the info before posting. You have been here long wnough to know better. . .
If the client/user is so undecided, you need to get to where you are self-sufficient and not need someone else to provide the answers.
d |
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
you are absolutely right Dick..!! Today i learned an awesome utility of ICEMAN...
Thanks a lot... i always try on my own and in case if i dont get anything proper then only i ask in our blog...!! Requirements are highly volatile..  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Requirements are highly volatile.. |
Yup, quite often. It is not always easy to get the users trained . . . 
This is why it is quite valuable to become self-sufficient.
Good Luck!
d |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
rohanthengal,
Here's a DFSORT job that will handle your changed requirement:
| Code: |
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB)
//IN2 DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=... output file (FB/80)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(11,5,A),SORTED
JOINKEYS F2=IN2,FIELDS=(1,5,A),SORTED
JOIN UNPAIRED,F2
REFORMAT FIELDS=(F2:1,80,?)
OPTION COPY
INREC IFOUTLEN=80,
IFTHEN=(WHEN=(81,1,CH,EQ,C'B'),OVERLAY=(28:C'Y'))
/*
|
|
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
| Thanks Frank it worked fantastic... i realised JOINKEYS is easier to play with than SPLICE.... |
|
| Back to top |
|
|
rohanthengal
Active User
Joined: 19 Mar 2009
Posts: 206
Location: Globe, India
|
|
|
|
| Frank, what is '?' in the above code ? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| From the Fine DFSORT Application Programming Guide wrote: |
? indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:
v 'B' - the key was found in F1 and F2.
v '1' - the key was found in F1, but not in F2.
v '2' - the key was found in F2, but not in F1.
Only one ? can be specified in the FIELDS operand. If ? is not the last item, it must be followed by F1: or F2:.
For TYPE=F joined records, the indicator can appear anywhere in the record. For example, each of the following is valid:
* Put indicator in position 1 of each joined record.
REFORMAT FIELDS=(?,F1:1,20,F2:5,8)
* Put indicator in position 21 of each joined record.
REFORMAT FIELDS=(F1:1,20,?,F1:31,9)
* Put indicator in position 25 of each joined record.
REFORMAT FIELDS=(F2:11,20,6,4,?)
For TYPE=V joined records, the indicator must appear in the fixed part of the record, that is, after the RDW and before the position without length fields. For example, the following is valid:
* Put indicator in position 5 of each joined record.
REFORMAT FIELDS=(F1:1,4,?,F1:11) |
|
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
| Back to top |
|
|
|
|
