Hi

I have two tables, one contains three indexes plus a date. The second table contains the main data. It links to the first to obtain the date.

Three links will be necessary as some records are joined on key K1, some on key K2, and some on K3.

TABLE1
K1
K2
K3
FLD1
FLD2
FLD3

TABLE2
K1
K2
K3
DATEFLD
INDEX K1 K2 K3


The only way I know to obtain the DATEFLD and place it in the main table is to do something like this-:

JOIN K1 IN TABLE1 TO K1 IN TABLE2
TABLE FILE TABLE1
PRINT
K1 K2 K3 FLD1 FLD2 FLD3
TABLE1.DATEFLD
ON TABLE HOLD AS TABLE1_K1
END

JOIN CLEAR
JOIN K2 IN TABLE1 TO K2 IN TABLE2
PRINT
K1 K2 K3 FLD1 FLD2 FLD3
TABLE1.DATEFLD
ON TABLE HOLD AS TABLE1_K2
END

JOIN CLEAR
JOIN K3 IN TABLE1 TO K3 IN TABLE2
PRINT
K1 K2 K3 FLD1 FLD2 FLD3
TABLE1.DATEFLD
ON TABLE HOLD AS TABLE1_K3
END

Then somehow merge the files TABLE1_K1, TABLE1_K2 and TABLE1_K2 together.

Would this be the correct idiom for this problem or is there any easier way?

e.g. write the datefld directly into TABLE1.
JOIN CLEAR
JOIN K1 IN TABLE1 TO K1 IN TABLE2
TABLE FILE TABLE1
PRINT
K1 K2 K3 FLD1 FLD2 FLD3
SET
TABLE1.DATEFLD = TABLE2.DATEFLD
END

Many thanks

Assuming you don't want to redo your joins or existing queries, use file concatenation:

 
TABLE FILE TABLE1_K1
PRINT 
K1 K2 K3 FLD1 FLD2 FLD3 DATEFLD
FILE
MORE
FILE TABLE1_K2
MORE
FILE TABLE1_K3
END
 

You can probably re-write your process to use it in fewer steps (without saving named intermediate hold files) if you turned your joins around.

This message has been edited. Last edited by: <Maryellen>,

Thanks N

Turning the joins around is not possible in this case.

all the TABLE1 rows that do not match with TABLE2,
must be concatenated with
with all the rows that do match using index one,
with all the rows that do match using index two.
(only two indexes now).

So I'll write out all rows from TABLE1 that do not match, and combine those with the two hold files containing the data from TABLE2.