[focus-l] Re: TRUNCATION
- From: "Harry Wykes" <harry@xxxxxxxxxxxxx>
- To: <focus-l@xxxxxxxxxxxxx>
- Date: Wed, 5 May 2004 20:05:01 +0100
Bob, Greetings.
I'm afraid I wasn't even born in 1950, but did my 'apprenticeship' on an ICL
1902 at Leeds Polytechnic in the 70s, punch cards and all.
>>I now have this thing down to two files; file A can have more than one
row per ssn; FILE B has only one row per ssn. I need to join them so
that each and every row in A has its' corresponding info from B. Many
times I've tried
This with JOIN with remarkable lack of success.
<<
Not sure what the problem is. If file A looks something like
FILENAME=FILEA ,SUFFIX=FOC
SEGNAME=SEG1 ,SEGTYPE=S1
FIELDNAME=K1 ,ALIAS=K1 ,FORMAT=I3 ,$
FIELDNAME=SSN ,ALIAS=SSN ,FORMAT=A10 ,$
and file B looks something like
FILENAME=FILEB ,SUFFIX=FOC
SEGNAME=SEG2 ,SEGTYPE=S1
FIELDNAME=SSN ,ALIAS=SSN ,FORMAT=A10 ,INDEX=I,$
FIELDNAME=NAME ,ALIAS=NAME ,FORMAT=A30 ,$
Then the following should work fine (in all the versions of FOCUS I know
of.)
JOIN SSN IN FILEA TO SSN IN FILEB AS JN1.
TABLE FILE FILEA
PRINT K1 SSN NAME
BY K1 NOPRINT
-* :-)
END
Should report all the records in FILEA with their matching records in FILEB,
something like the following...
K1 SSN NAME
-- --- ----
1 A100 FRED BLOGGS
2 A100 FRED BLOGGS
3 B200 JOHN DOE
4 B300 ATTILA HUN
5 A100 FRED BLOGGS
6 C400 JOHN LENNON
If there are SSNs in FILE A which have no corresponding SSNs in FILE B then
you will have to SET ALL=ON or SET ALL=PASS, depending, to retrieve the
'short records'.
>> Also match seems to work sometimes in this situation. <<
If should work always. MATCH is an incredibly powerful tool. Compare and
contrast to similar SQL syntax. However, it can be a bit heavy on IO with
large files.
Regards, Harry Wykes, UK
Other related posts:
