I have two file like:

File 1:
A a
A b
B a
B B


File 2:
A 1
A 2
B 1
B 2

I want to join the two files to get the following output:

A a 1
A a 2
A b 1
A b 2
B a 1
B a 2
B b 1
B b 2

Is this possible via match.

Your suggestions are appreciated.

This message has been edited. Last edited by: Shrikant,

Have you even tried a normal join command?
Somthing like: JOIN A IN A TO ALL A IN B AS J1 ??

Yes, I have tried that. As usual it always fails with Duplicates in 'From' field error.

Hi,

Perhaps this will work: if you Join table A to table B hold as C, then B to table A hold as D, then perform a union of C and D. I think in focus you use the MORE command as a union, if not using SQL passthru.

Hope this makes sense! ;-)

Jinx.

I think that GamP's idea is the way to go. See:

-* Simulate File A
DEFINE FILE CAR
CTR/I4 WITH CAR = CTR + 1;
CODE1/A1 WITH CAR = IF CTR EQ 1 OR 2 THEN 'A' ELSE 'B';
CODE2/A1 WITH CAR = IF CTR EQ 1 OR 3 THEN 'a' ELSE 'b';
END
TABLE FILE CAR
PRINT CODE2
BY CODE1
WHERE RECORDLIMIT EQ 4
ON TABLE HOLD AS A
END

-* Simulate File B
DEFINE FILE CAR
CTR/I4 WITH CAR = CTR + 1;
CODE1/A1 WITH CAR = IF CTR EQ 1 OR 2 THEN 'A' ELSE 'B';
CODE3/I4 WITH CAR = IF CTR EQ 1 OR 3 THEN 1 ELSE 2;
END
TABLE FILE CAR
PRINT CODE3
BY CODE1
WHERE RECORDLIMIT EQ 4
ON TABLE HOLD AS B FORMAT FOCUS INDEX CODE1
END

-*------------------------------------
JOIN CLEAR *
JOIN CODE1 IN A TO ALL CODE1 IN B AS J1

TABLE FILE A
PRINT CODE1
      CODE2
      CODE3
END

Why not:

JOIN LEFT_OUTER CODE1 IN A TO ALL CODE1 IN B AS J1
-RUN

quote:

Duplicates in 'From' field error

It always helps if you tell us up front what the situation is: getting errors, type of database used, what did I do already, etc. It saves time, both for you and for us.
This error message tells me that the files you're trying to access are flat files. And these have very strict rules when it comes to joining.
If you convert them to focus files or rdbms files table - whichever has your preference, the error goes away and you get the desired output.
Below code shows exactly that:

FILEDEF MAS_A DISK file_a.mas
FILEDEF MAS_B DISK file_b.mas
FILEDEF FILE_A DISK file_a.ftm
FILEDEF FILE_B DISK file_b.ftm
-RUN
-WRITE MAS_A FILENAME=FILE_A, SUFFIX=FIX, $
-WRITE MAS_A SEGNAME=FILE_A, $
-WRITE MAS_A FIELDNAME=FIELDA1, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_A FIELDNAME=FIELDA2, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_B FILENAME=FILE_B, SUFFIX=FIX, $
-WRITE MAS_B SEGNAME=FILE_B, $
-WRITE MAS_B FIELDNAME=FIELDB1, FORMAT=A1, ACTUAL=A1, $
-WRITE MAS_B FIELDNAME=FIELDB2, FORMAT=A1, ACTUAL=A1, $
-WRITE FILE_A Aa
-WRITE FILE_A Ab
-WRITE FILE_A Ba
-WRITE FILE_A Bb
-WRITE FILE_B A1
-WRITE FILE_B A2
-WRITE FILE_B B1
-WRITE FILE_B B2
-RUN

TABLE FILE FILE_A
PRINT *
ON TABLE HOLD AS FA FORMAT FOCUS
END
TABLE FILE FILE_B
PRINT *
ON TABLE HOLD AS FB FORMAT FOCUS INDEX FIELDB1
END
JOIN FIELDA1 IN FA TO ALL FIELDB1 IN FB AS J1
TABLE FILE FA
PRINT FIELDA1
FIELDA2 FIELDB2
END

Thanks GamP

This works perfectly.

The files that I was using were created in previous steps of FOCUS processing.