Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Joining fields from two files on a key

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Wed Apr 25, 2007 11:25 am    Post subject: Joining fields from two files on a key
Reply with quote

Hi all,

My requirement is,
I have two files with a key in bytes 1-3 and data in bytes 5-9.
File A has the following records:
000 $$$$$
001 AAAAA
002 CCCCC
003 EEEEE
004 GGGGG
and File B has the following records:
001 BBBBB
003 DDDDD
004 FFFFF
005 HHHHH
I want to join the data fields for pairs of records with the same key to get the following output:
001 AAAAA BBBBB
003 EEEEE DDDDD
004 GGGGG FFFFF

I am trying to use this JCL,

//DFSORT EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=
//DFSMSG DD SYSOUT=
//IN1 DD DSN=... file 1
//IN2 DD DSN=... file 2
//TMP1 DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA
//OUT DD DSN=... output file
//TOOLIN DD *
 For this example, the fields (p,m) are as follows:
 IN1: sort key - 1,3
 f1fld - 5,5
 IN2: sort key - 1,3
 f2fld - 5,5

 Reformat the IN1 data set so it can be spliced
COPY FROM(IN1) TO(TMP1) USING(CPY1)
 Reformat the IN2 data set so it can be spliced
COPY FROM(IN2) TO(TMP1) USING(CPY2)
 Splice records with matching IN1/IN2 keys
SPLICE FROM(TMP1) TO(OUT) ON(1,3,CH) WITH(11,5)
/*
//CPY1CNTL DD *
 Use OUTREC to create |key |f1fld |blank|
OUTREC FIELDS=(1:1,3,5:5,5,11:5X)
/*
//CPY2CNTL DD *
 Use OUTREC to create:|key |blank |f2fld|
OUTREC FIELDS=(1:1,3,11:5,5)
/*

The above all information i was able to get from the IBM pdf document.

but I am constantly gettign the error as
CPY1CNTL :
OUTREC FIELDS=(1:1,3,5:5,5,11:5X)
*
WER428I CALLER-PROVIDED IDENTIFIER IS "0001"
WER001A COL 1 OR 1-15 NOT BLANK
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE


Is it any way related to the availability of data in the input file? If it is so then my whole need is gone..I am really confused by this error message.Please do help me.[/img]
Back to top
View user's profile Send private message

kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Wed Apr 25, 2007 12:42 pm    Post subject:
Reply with quote

My own requirement is like,

File A
----+----1----+----2----+----3----+----4---
Z002 014130..N
Z002 015300..N
Z007A010100..N
Z007A011700..N
Z007A013920..N
Z007A016100..N
3710A02310 ..Y7006570521
3710A02330 ..Y7006570521
3710A02350 ..Y7006570521
3710A02370 ..Y7006570521
3710A02410 ..Y7006570521
3710A02510 ..Y7006570521
4127A013100..N
4127A013330..N

File B
----+----1----+----2----+----3----+----4---
Z002 01...2018LIAB 01QS
Z007A01...4235PROP 01XS
3710A02...2157CAS 02CCC
4127A01...1602WC-EL01XOC

The above two files are my input files. I want the output to be like
----+----1----+----2----+----3----+----4---
Z002 014130..N 2018LIAB 01QS
Z002 015300..N 2018LIAB 01QS
Z007A010100..N 4235PROP 01XS
Z007A011700..N 4235PROP 01XS
Z007A013920..N 4235PROP 01XS
Z007A016100..N 4235PROP 01XS
3710A02310 ..Y7006570521 2157CAS 02CCC
3710A02330 ..Y7006570521 2157CAS 02CCC
3710A02350 ..Y7006570521 2157CAS 02CCC
3710A02370 ..Y7006570521 2157CAS 02CCC
3710A02410 ..Y7006570521 2157CAS 02CCC
3710A02510 ..Y7006570521 2157CAS 02CCC
4127A013100..N 1602WC-EL01XOC
4127A013330..N 1602WC-EL01XOC

i.e my requirement is to search for the key from 1 to 7th poisition from File B to File A and if a match is found then at that same record, the value from 11 to 24 of File B has be printed in 30th to 43rd position of File A. Am I making sense in my question? icon_question.gif Plase do reply.
Back to top
View user's profile Send private message
William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Wed Apr 25, 2007 3:11 pm    Post subject:
Reply with quote

Try reading the error:
WER001A COL 1 OR 1-15 NOT BLANK
the sort control statement can not start in column 1.
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Wed Apr 25, 2007 3:26 pm    Post subject: Thanks for the reply.
Reply with quote

Thank you so much..... icon_biggrin.gif
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Wed Apr 25, 2007 3:40 pm    Post subject: pls help me out
Reply with quote

but i got the results as

Code:
----+----1----+----2----+----3----+----4---
Z002 014130..N
Z002 015300..N 2018LIAB 01QS
Z007A010100..N
Z007A011700..N
Z007A013920..N
Z007A016100..N 4235PROP 01XS
3710A02310 ..Y7006570521
3710A02330 ..Y7006570521 
3710A02350 ..Y7006570521
3710A02370 ..Y7006570521
3710A02410 ..Y7006570521
3710A02510 ..Y7006570521 2157CAS 02CCC
4127A013100..N
4127A013330..N 1602WC-EL01XOC

I want all the matching records to have the same fields as in 30th to 43rd position. But only the last field in each group only has the value i desired.

I have used

//TOOLIN DD *
COPY FROM(FILE1) TO(TMP1) USING(CPY1)
COPY FROM(FILE2) TO(TMP1) USING(CPY2)
SPLICE FROM(TMP1) TO(FILE3) ON(1,7,CH) WITHALL WITH(27,14)
/*
//CPY1CNTL DD *
OUTREC FIELDS=(1:1,7,8:8,19,27:14X)
/*
//CPY2CNTL DD *
OUTREC FIELDS=(1:1,7,27:27,14)
/*

as JCL

Please help me out
Back to top
View user's profile Send private message
kaleelahamed

New User


Joined: 08 Jun 2006
Posts: 45
Location: Trumbull, CT

PostPosted: Thu Apr 26, 2007 8:50 am    Post subject:
Reply with quote

THE OUTPUT WAS LIKE

----+----1----+----2----+----3----+----4---
Z002 014130..N
Z002 015300..N 2018LIAB 01QS
Z007A010100..N
Z007A011700..N
Z007A013920..N
Z007A016100..N 4235PROP 01XS
3710A02310 ..Y7006570521
3710A02330 ..Y7006570521
3710A02350 ..Y7006570521
3710A02370 ..Y7006570521
3710A02410 ..Y7006570521
3710A02510 ..Y7006570521 2157CAS 02CCC
4127A013100..N
4127A013330..N 1602WC-EL01XOC

whereaes i need it to be
----+----1----+----2----+----3----+----4---
Z002 014130..N 2018LIAB 01QS
Z002 015300..N 2018LIAB 01QS
Z007A010100..N 4235PROP 01XS
Z007A011700..N 4235PROP 01XS
Z007A013920..N 4235PROP 01XS
Z007A016100..N 4235PROP 01XS
3710A02310 ..Y7006570521 2157CAS 02CCC
3710A02330 ..Y7006570521 2157CAS 02CCC
3710A02350 ..Y7006570521 2157CAS 02CCC
3710A02370 ..Y7006570521 2157CAS 02CCC
3710A02410 ..Y7006570521 2157CAS 02CCC
3710A02510 ..Y7006570521 2157CAS 02CCC
4127A013100..N 1602WC-EL01XOC
4127A013330..N 1602WC-EL01XOC

please help me out...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us