|
View previous topic :: View next topic
|
| Author |
Message |
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
I have 2 files f1,f2.I have to fetch unmatched records from file f2.
to the file f2 data I have to append date from file f3 and party key from other file f4.
is that possible for me to do all the things in a single sort step. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| wouldn' t it be smarter to use a more descriptive title ? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
is that possible for me to do all the things in a single sort step.
|
yes |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
I have 2 files f1,f2.I have to fetch unmatched records whose status code is 5 from file f2 .To the file f2 data I have to append date from file f3 for this a new join step I have to write and for party key from other file f4 i ve to write an other join.is that possible for me to do all the things in a single sort step.
f1
jeba 5
kiruba 5
f2
jeba 5
kiruba 5
praba 3
selva 5
leroy 5
f3
20121212
f4
Selva 90909090
leroy 80808080
Desired output
Selva 20121212 90909090
leroy 20121212 80808080 |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
| The data in f1 will be always in F2 .. But F2 may or may not ve excess record |
|
| Back to top |
|
|
elango_K
New User
Joined: 18 Aug 2011
Posts: 44
Location: India
|
|
|
|
You did not get my third question .
My question was whether there are duplicates in file 1 and duplicates in File2.
Like
F1:
| Code: |
jeba 5
jeba 5
kiruba 5
kiruba 5
|
F2:
| Code: |
jeba 5
kiruba 5
praba 3
selva 5
leroy 5
praba 3
selva 5
leroy 5
|
|
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
| No duplicates as u mentioned . |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
| There will not be any duplicates within the files . |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
d_sarlie,
1) What is the LRECL and RECFM for each file?
2) Does FILE1 only have records with record type 5? Is it possible to have any other record type in FILE1?
3) What are the field positions for each fields in each file. Also provide field definition for each field as well.
4) I am assuming there can be only 1 record in the FILE3. Let me know if that's not correct.
5) Do you care about upper case or lower case, when matching unmatched records from FILE2 with FILE4 on name for the population of party key? For example, Selva vs. selva
Last but not the least, my 2 cents....
You should be concentrating on the most efficient solution which is easy to maintain as well,and not something written in a single sort step.
Thanks, |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
Now I have got unmatched record from f1 and f2 my resultant output is
selva 5
leroy 5
My File 3 has date. I want to include the date in both the records
f3
20121212
selva 5 20121212
leroy 5 20121212 |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Elango and sqlcode1
jeba thinks that you are dentists |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| d_sarlie wrote: |
| dbzTHEdinosauer wrote: |
jeba thinks that you are dentists |
 |
Glad you are laughing when they are trying to pull the teeth. However you need to open your mouth completely, so that the dentist can finish his work.
Go back and answer EVERY question Sqlcode1 has asked. |
|
| Back to top |
|
|
sqlcode1
Active Member
Joined: 08 Apr 2010
Posts: 577
Location: USA
|
|
|
|
d_sarlie,
Personally, I will never turn this to production but see if below works... Specially beware of writting large number of records to &&TMP file. Field lengths and positions assumed.
EDITED :- Kolusu, sorry our posts crossed path. My solution assumes so many things which OP didn't answer originally. Feel free to delete the post if needed.
| Code: |
//STEP0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
20121212
/*
//SORTOUT DD DSN=&&DT,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN DD *
OPTION COPY
OUTFIL REMOVECC,NODETAIL,BUILD=(80X),
TRAILER1=('DT,''',1,8,'''')
/*
//*
//STEP0002 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//SYMNAMES DD DSN=&&DT,DISP=(OLD,PASS)
//IN1 DD *
JEBA 5
KIRUBA 5
/*
//IN2 DD *
JEBA 5
KIRUBA 5
PRABA 3
SELVA 5
LEROY 5
/*
//IN3 DD *
SELVA 90909090
LEROY 80808080
/*
//TMP DD DSN=&&TMP,DISP=(MOD,PASS),SPACE=(CYL,(10,5),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY JKFROM TO(TMP) USING(CTL1)
COPY JKFROM USING(CTL2)
/*
//CTL1CNTL DD *
JOINKEYS F1=IN1,FIELDS=(01,16,A),TASKID=T1
JOINKEYS F2=IN2,FIELDS=(01,16,A),TASKID=T1
JOIN UNPAIRED,F2,ONLY
REFORMAT FIELDS=(F2:1,16)
OUTFIL FNAMES=TMP,BUILD=(1,16,DT,X)
/*
//CTL2CNTL DD *
JOINKEYS F1=TMP,FIELDS=(01,16,A),SORTED
JOINKEYS F2=IN3,FIELDS=(01,16,A)
REFORMAT FIELDS=(F1:01,25,F2:17,08)
OUTFIL FNAMES=OUT
/*
//T1F1CNTL DD *
INCLUDE COND=(17,1,CH,EQ,C'5')
/*
|
OUTPUT
| Code: |
LEROY 20121212 80808080
SELVA 20121212 90909090
|
Thanks, |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
I am getting this error.
********************************* TOP OF DATA *******************
SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT302I SYMNAMES STATEMENTS IN USE
DT,'20121212'
*
*** SYMBOL IS A RESERVED WORD ***
SYT303E SYMNAMES STATEMENT ERROR
SYT304E SYMNAMES ERRORS FOUND
SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
******************************** BOTTOM OF DATA ***************** |
|
| Back to top |
|
|
d_sarlie
New User
Joined: 22 Apr 2008
Posts: 42
Location: coimbatore
|
|
|
|
| d_sarlie wrote: |
I am getting this error.
********************************* TOP OF DATA *******************
SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT302I SYMNAMES STATEMENTS IN USE
DT,'20121212'
*
*** SYMBOL IS A RESERVED WORD ***
SYT303E SYMNAMES STATEMENT ERROR
SYT304E SYMNAMES ERRORS FOUND
SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12
******************************** BOTTOM OF DATA ***************** |
This error I edited with DAT instead DA
Now getting an other error
SYT000I SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007 SYNCSORT INC.
SYT001I INITIAL PROCESSING MODE IS "STOP"
SYT302I SYMNAMES STATEMENTS IN USE
SYT002I "TOOLIN" INTERFACE BEING USED
COPY JKFROM TO(OUT1) USING(CTL1)
SYT005I PERFORM NECESSARY SYMBOL SUBSTITUTION
COPY JKFROM TO(OUT1) USING(CTL1)
SYT050E INVALID OPERAND ON "COPY" STATEMENT
SYT030I OPERATION COMPLETED WITH RETURN CODE 12
SYT015I PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE
COPY JKFROM USING(CTL2)
SYT005I PERFORM NECESSARY SYMBOL SUBSTITUTION
COPY JKFROM USING(CTL2)
SYT050E INVALID OPERAND ON "COPY" STATEMENT
SYT004I SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12 |
|
| Back to top |
|
|
gcicchet
Senior Member
Joined: 28 Jul 2006
Posts: 1702
Location: Australia
|
|
|
|
Hi,
this is an old quote from the master himself, Frank Yaeger
| Quote: |
The job I showed works fine with DFSORT which supports JKFROM.
However, the SYT messages indicate you're using Syncsort, not DFSORT.
Syncsort does not support JKFROM.
|
Gerry |
|
| Back to top |
|
|
|
|
