report 1:
TABLE FILE SQLOUT
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE NOTOTAL
ON TABLE HOLD AS PARTCOMP1 FORMAT FOCUS INDEX PLANT DEPT PARTNO

report2
TABLE FILE SQLOUT
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE NOTOTAL
ON TABLE HOLD AS PARTCOMP2 FORMAT FOCUS INDEX PLANT DEPT PARTNO

JOIN
PARTCOMP1.PLANT IN PARTCOMP1 AND PARTCOMP1.DEPT AND PARTCOMP1.PARTNO IN PARTCOMP1
TO MULTIPLE PARTCOMP2.PLANT AND PARTCOMP2.DEPT AND PARTCOMP2.PARTNO
IN PARTCOMP2 AS J0 TAG J0
END

get the following errors
(FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND:
(FOC1517) UNRECOGNIZED COMMAND TO MULTIPLE PARTCOMP2.PLANT AND PARTCOMP2.DEPT
AND PARTCOMP2.PARTNO
(FOC1517) UNRECOGNIZED COMMAND IN PARTCOMP2 AS J0 TAG J0
0 ERROR AT OR NEAR LINE 228 IN PROCEDURE part_performance
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: JO
BYPASSING TO END OF COMMAND

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

JOIN
PARTCOMP1.PLANT IN PARTCOMP1 AND PARTCOMP1.DEPT AND PARTCOMP1.PARTNO IN PARTCOMP1
TO MULTIPLE PARTCOMP2.PLANT AND PARTCOMP2.DEPT AND PARTCOMP2.PARTNO
IN PARTCOMP2 AS J0 TAG J0
END

Is this a Many to Many JOIN?

No it's one to one, so I took off the multiple and in.
Now I get LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT: PARTCOMP2
IF i LOOK AT THE TWO MASTERFILES THE COLUMNS MATCH

Dave,
you may need to sort your data prior to joining the 2 fields and since this is a one to one relationship simply SUM 'JoinField' BY 'JoinField' to get one instance of each value.


ERINP

Correct me if I am wrong but I don't think multi-field joins are allowed when joining to a .foc files.

You have to use XFOCUS files and set up a multi-field index and I believe that using XFOCUS files is a license add-on.

Your code should look like this:

JOIN PARTCOMP1.PLANT IN PARTCOMP1 TO ALL PARTCOMP2.PLANT IN PARTCOMP2 AS J0 TAG J0
-*
TABLE FILE PARTCOMP1
PRINT .....
   BY .....
WHERE DEPT   EQ J0.DEPT
  AND PARTNO EQ J0.PARTNO
....
END

Another way would be to use a COMPUTE in each TABLE command to concatenate PLANT, DEPT and PARTNO into a single field, index that field instead and join on that FIELD. Since you are not JOINing to PARTCOMP1, there is no need for the index in that HOLD FORMAT FOCUS. You only need the index on the second one.

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

DaveZ,
jgelona makes an interesting point... I think you can use the multi field index but only for one of the reports. I noticed you are using 7.7.1 so I'm not sure if you have the ability in that release to use more than one index in a report. In 7.6.x I am only allowed to use one Index. With that being said you can follow jgelona's suggestion or Index the first file and SUMmarize the second file prior to making your JOIN.

 
report 1:
TABLE FILE SQLOUT
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE NOTOTAL
ON TABLE HOLD AS PARTCOMP1 FORMAT FOCUS INDEX PLANT DEPT PARTNO

report2
TABLE FILE SQLOUT
SUM
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE NOTOTAL
ON TABLE HOLD AS PARTCOMP2 

I TRIED THIS JOIN:
JOIN CLEAR *
JOIN
PARTCOMP1.PLANT IN PARTCOMP1 TO ALL PARTCOMP2.PLANT IN PARTCOMP2 AS J1 TAG J1
END

GET THIS MESSAGE:
0 ERROR AT OR NEAR LINE 116 IN PROCEDURE part_performance
(FOC002) A WORD IS NOT RECOGNIZED: IN


which is the 'in' within the join..the more I try to understand joins the more I get confused
why is this not working?

Thank for all the help

TABLE FILE SQLOUT
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE HOLD AS PARTCOMP1
END
-RUN
-******************************
-* report2
-******************************
TABLE FILE SQLOUT
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
ON TABLE HOLD AS PARTCOMP2
END
-RUN

JOIN PLANT AND DEPT AND PARTNO IN PARTCOMP1 TO ALL PLANT AND DEPT AND PARTNO IN PARTCOMP2 AS J0
-RUN
-****************************
TABLE FILE PARTCOMP1
ETC...

I've tried just about everything and still can't get it right.
A little background - the report is used to compare part efficiency from period to period.
The plant and department in both files are the same but the partno my vary depending if work was done on that part for that period.

PLANT J1DEPT J1PARTNO DL ESH J1EFFICIENCY J2PARTNO J3DL J3ESH J3EFFICIENCY
6 715 P30126 4.07 .00 1.53 P30126 4.07 .00 .00
6 715 P30126 4.31 4.00 1.53 P30126 4.31 4.00 .93
6 715 P30126 29.51 36.25 1.53 P30126 29.51 36.25 1.23
6 715 P30126 39.76 44.95 1.53 P30126 39.76 44.95 1.13
6 715 P30126 49.56 42.05 1.53 P30126 49.56 42.05 .85
6 715 P30126 34.29 46.40 1.53 P30126 34.29 46.40 1.35
6 715 P30126 41.94 39.15 1.53 P30126 41.94 39.15 .93
6 715 P30126 33.81 33.35 1.53 P30126 33.81 33.35 .99
6 715 P30126 36.65 36.25 1.53 P30126 36.65 36.25 .99
6 715 P30126 36.52 33.35 1.53 P30126 36.52 33.35 .

you can see the partno is repeating all the dl, esh is rollup to the part level is the view (masterfile)

TABLE FILE DEV_STUDIO_PART_COMPARE
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
WHERE PLANT = '6' AND DEPT = '715' AND FISCAL_QUARTER = 1 AND FISCAL_YEAR = 2012
-* AND RNTITY_CODE = 'CORP'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS PARTCOMP1 FORMAT FOCUS INDEX PLANT DEPT PARTNO
END
-*END1
TABLE FILE DEV_STUDIO_PART_COMPARE
PRINT
DL
ESH
EFFICIENCY
BY PLANT
BY DEPT
BY PARTNO
WHERE PLANT = '6' AND DEPT = '715' AND FISCAL_QUARTER = 1 AND FISCAL_YEAR = 2012
-* AND RNTITY_CODE = 'CORP'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT EXL2K
ON TABLE HOLD AS PARTCOMP2 FORMAT FOCUS INDEX PLANT DEPT PARTNO
END
-*END1
JOIN CLEAR *
JOIN
LEFT_OUTER PLANT IN PARTCOMP1 TO PLANT IN PARTCOMP2 AS J1 TAG J1
END
JOIN
LEFT_OUTER DEPT IN PARTCOMP1 TO DEPT IN PARTCOMP2 AS J2 TAG J2
END
JOIN
LEFT_OUTER PARTNO IN PARTCOMP1 TO PARTNO IN PARTCOMP2 AS J3 TAG J3
END
TABLE FILE PARTCOMP1
PRINT
'PARTCOMP2.SEG01.PLANT'
'PARTCOMP2.SEG01.J1DEPT'
'PARTCOMP2.SEG01.J1PARTNO'
'PARTCOMP1.SEG01.DL'
'PARTCOMP1.SEG01.ESH'
'PARTCOMP2.SEG01.J1EFFICIENCY'
'PARTCOMP2.SEG01.PARTNO'
'PARTCOMP2.SEG01.J3DL'
'PARTCOMP2.SEG01.J3ESH'
'PARTCOMP2.SEG01.J3EFFICIENCY'
ON TABLE NOTOTAL
END

Thanks to every one for all the help, I ended up using MATCH and it worked perfectly

did you ever try ON TABLE HOLD AS ... FORMAT XFOCUS as JGelona suggested?