I'm new to studying FOCUS language and attempting to join three tables together. The only hitch is that I need to tweak the joining columns prior to the join. I have used a Virtual field before to join two tables without issue. Adding a third table would seem simple enough but I can't seem to find any documentation on how to do this. Here's a simplified example. When I run this I receive errors that "FIELD2" in Table3 IS NOT RECOGNIZED AND ALSO vFIELD2 USED IN JOIN CANNOT BE FOUND IN THE FILE. How should I do this? I would prefer NOT to modify the master file as other queries have already been created against the masters.
-------------------------------------------------------------------------------------------------------------------------------------
JOIN vFIELD1 WITH FIELD1 IN TABLE1 TO FIELD1 IN TABLE2 TAG J1 AS JOIN1
JOIN vFIELD2 WITH FIELD2 IN TABLE2 TO FIELD2 IN TABLE3 TAG J2 AS JOIN2

DEFINE FILE TABLE1
vFIELD1/A10V = TRIM('L',FIELD1,10,'0',1,vFIELD1);
vFIELD2/A10V = TRIM('L',FIELD2,10,'0',1,vFIELD2);
END

TABLE FILE TABLE1
PRINT
J1.FIELD1
J2.FIELD2
ON TABLE PCHOLD FORMAT HTML
END
-------------------------------------------------------------------------------------------------------------------------------------

With multiple joins, the host is always the same.

Try changing:

JOIN vFIELD2 WITH FIELD2 IN TABLE2 TO

to

JOIN vFIELD2 WITH FIELD2 IN TABLE1 TO

Thanks but I have Three tables to join:

The join from Table2 to Table3 uses a field from Table2 (not Table1) to join Table3. If the host (Table1) is always the same how can you reference anything from Table2?

(TABLE1)		(TABLE2)		(TABLE3)
FIELD1	-JOIN-	FIELD1		FIELD1
FIELD2		FIELD2	-JOIN-	FIELD2
FIELD3		FIELD3		FIELD3

When you join from table 1 that has already been joined to table 2, you are joining from any of the fields from table 1 or 2.

I would suggest that you have a look at the docuemntation on joins

Phil,

Waz is correct.
Once you have issued your first join, all the fields of TABLE2 become, virtually, fields of TABLE1. If you want to join TABLE3 to the joined structure, you join to TABLE1.
Try it.