I would like to create a 3 table (perhaps more if I can get this to work) join. I've been able to do this using the standard join syntax where i join a to b and then using a field in b i can join to c. However, now my join b to c is a conditional join and it does not seem to be working. no matter what i try i always get an error.

my syntax is:

JOIN INNER FILE ORD_OVERTIME_SUMMARY AT EMPLOYEE_ID
TO UNIQUE FILE ORD_EMPLOYEE AT EMPLOYEE_ID
WHERE ORD_OVERTIME_SUMMARY.EMPLOYEE_ID EQ ORD_EMPLOYEE.EMPLOYEE_ID ;
END

JOIN INNER FILE ORD_OVERTIME_SUMMARY AT ORD_EMPLOYEE.EMPLOYEE_NAME
TO UNIQUE
FILE ORD_CODES AT ORD_CODES.CODE_TYPE_NUMBER

WHERE ORD_CODES.CODE_TYPE_NUMBER EQ '3' ;
WHERE ORD_EMPLOYEE.PAYING_AGENCY EQ ORD_CODES.CODE_VALUE;

END


Running the above returns a (FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: ORD_EMPLOYEE.EMPLOYEE_NAME

Note EMPLOYEE_NAME in table ORD_EMPLOYEE which I should have after the first join.

ORD_CODES is a code table that has translations for many different kinds of fields. Each translation has a number identifying the type of translation. For example "3' is for PAYING AGENCIES. 4 might be for Departments, etc. I know I can break the codes table into many different tables so I can eliminate the need to use a conditional Join. I also know I can do many TABLE and HOLD for each type of code and then join to the HOLD files but this will be slow and this is for an online reporting application. Are these kinds of Joins doable? Please help. I am under the gun to get this application up and running.

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

The second JOIN is clobbering the first JOIN. You need to use the AS keyword to differentiate between the JOINs. Additionally, You can use the TAG keyword to prefix the column names of each join. Please take a look at the JOIN documentation in the WebFOCUS Language Documentation.

THANK YOU!!!!!! ALL WORKING!!!!

I've been using FOCUS for 20 years and forgot when you don't name the JOIN the first goes away. I was trying to simplify the code and I removed the tags and join names.