|
View previous topic :: View next topic
|
| Author |
Message |
Mike 01Hawk
New User
Joined: 02 Oct 2006
Posts: 4
Location: Tulsa
|
|
|
|
Input File 1 (list of everyone)
1 Mary New York
2 Jon New York
3 Sally New York
4 Tom London
5 Jack London
Input File 2 (Master State/Country)
New York - US
London - UK
Output-
What I'm getting
1 Mary US
4 Tom UK
But what I really want
1 Mary US
2 Jon US
3 Sally US
4 Tom UK
5 Jack UK
My code:
002400,FILE FILEA FB(18 27990)
002500,FA-FILLER 1 10 A
002600,FA-KEY 11 8 A
002700,****************************************
002800,FILE FILEB FB(10 27990)
002900,FB-KEY 1 8 A
003000,FB-FILLER 9 2 A
003100,****************************************
003200,FILE FILEC FB(12 27996)
003300,FILLER-A 1 10 A
003400,FILLER-B 11 2 A
003500,****************************************
003600,JOB INPUT (FILEA KEY(FA-KEY) +
003700, FILEB KEY(FB-KEY))
003800,IF MATCHED
003900, MOVE FA-FILLER TO FILLER-A
004000, MOVE FB-FILLER TO FILLER-B
004100, PUT FILEC
004200,END-IF
Many thanks in advance,
Mike |
|
| Back to top |
|
 |
Mike 01Hawk
New User
Joined: 02 Oct 2006
Posts: 4
Location: Tulsa
|
|
|
|
| And what sucks is I could get this finished in 2 seconds if I could use Ideal Dataquery |
|
| Back to top |
|
|
superk
Global Moderator
Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
|
|
|
|
| ... or even quicker if you were to use a SORT job ... |
|
| Back to top |
|
|
Mike 01Hawk
New User
Joined: 02 Oct 2006
Posts: 4
Location: Tulsa
|
|
|
|
| Sort can do a 1-to-many merge??? |
|
| Back to top |
|
|
Douglas Wilder
Active User
.jpg)
Joined: 28 Nov 2006
Posts: 305
Location: Deerfield IL
|
|
|
|
I believe that changing:
| Code: |
JOB INPUT (FILEA KEY(FA-KEY) +
FILEB KEY(FB-KEY))
|
To:
| Code: |
JOB INPUT (FILEB KEY(FB-KEY) +
FILEA KEY(FA-KEY))
|
Will do what you want.
For matching the order of the files does affect how duplicates are handled. |
|
| Back to top |
|
|
Douglas Wilder
Active User
Joined: 28 Nov 2006
Posts: 305
Location: Deerfield IL
|
|
|
|
| Sorry I was wrong. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| And what sucks is I could get this finished in 2 seconds if I could use Ideal Dataquery |
Is the data already in Datacom tables? Easytrieve will run directly against Datacom (as do IDEAL and DataQuery). |
|
| Back to top |
|
|
Mike 01Hawk
New User
Joined: 02 Oct 2006
Posts: 4
Location: Tulsa
|
|
|
|
Mr. Scherrer
They are extracted datasets from Datacom tables, I don't believe our company has it setup to were we can access the tables directly themselves.
Regardless... I'll just set up an IDEAL program to read an input file and bounce against our tables, I have other validations/derivations and I'm more comfortable doing them w/ a pgm.
Thanks
Mike |
|
| Back to top |
|
|
Douglas Wilder
Active User
Joined: 28 Nov 2006
Posts: 305
Location: Deerfield IL
|
|
|
|
| My prior solution works only if the files are both sorted by city, in this case both files are in order by city (descending) so it works fine. Just switch the order of the file as I specified before. |
|
| Back to top |
|
|
lcmontanez
New User
Joined: 19 Jun 2007
Posts: 50
Location: Chicago
|
|
|
|
Another possible solution is to code the second file as an external table
use
SEARCH xxxxxx WITH FA-KEY GIVING FILLER-B
No sort needed. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Mike,
| Quote: |
Regardless... I'll just set up an IDEAL program to read an input file and bounce against our tables, I have other validations/derivations and I'm more comfortable doing them w/ a pgm.
Thanks
Mike |
You're welcome 
Personally, i like the IDEAL alternative. Having Database dataviews and sequential dataviews that use the same "coding" is quite handy 
d |
|
| Back to top |
|
|
superk
Global Moderator
Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA
|
|
|
|
| Mike 01Hawk wrote: |
| Sort can do a 1-to-many merge??? |
Sure. Here's one scenario I worked out using DFSORT:
| Code: |
//STEPXXXX EXEC PGM=ICETOOL
//IN DD *
NEW YORK - US
LONDON - UK
/*
// DD *
1 MARY NEW YORK
2 JON NEW YORK
3 SALLY NEW YORK
4 TOM LONDON
5 JACK LONDON
/*
//T1 DD DSN=&&T1,DISP=(,PASS),UNIT=VIO
//T2 DD DSN=&&T2,DISP=(,PASS),UNIT=VIO
//OUT DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN) USING(CTL1)
SPLICE FROM(T1) TO(T2) ON(10,10,CH) WITH(1,9) WITH(81,8) WITHALL
SORT FROM(T2) USING(CTL2)
/*
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=INIT,BUILD=(1,80,81:SEQNUM,8,ZD)),
IFTHEN=(WHEN=(11,1,CH,EQ,C'-'),BUILD=(10:1,10,22:13,2))
SORT FIELDS=(10,10,CH,A)
OUTFIL FNAMES=T1
/*
//CTL2CNTL DD *
SORT FIELDS=(81,8,BI,A)
OUTFIL FNAMES=OUT,BUILD=(1,80)
/*
//*
|
|
|
| Back to top |
|
|
|
|
