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 COMMANDThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.7.1 Windows 2000 Output: Excel and PDF
August 28, 2011, 03:33 PM
j.gross
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
August 29, 2011, 09:55 AM
WFLurker
Is this a Many to Many JOIN?
WF 8105M - Portal, Dashboard - Rcaster, Data Migrator - Windows 2012 Client Server - Dev/App Studio 8105 - Data: SQL, Oracle, Neteeza,MVS
August 29, 2011, 12:31 PM
DaveZ
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
WebFOCUS 7.7.1 Windows 2000 Output: Excel and PDF
August 30, 2011, 07:39 AM
ERINP
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
WebFOCUS 7.6.9
Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5 Reporting Server OS/400 V5R4M0 Outputs: HTML, Excel, PDF, CSV, and Flat Files
August 30, 2011, 08:30 AM
jgelona
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,
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
August 30, 2011, 10:04 AM
ERINP
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
WebFOCUS 7.6.9
Reporting client Windows 2003 Service pack 2 using IIS and TomCat 5.5 Reporting Server OS/400 V5R4M0 Outputs: HTML, Excel, PDF, CSV, and Flat Files
August 30, 2011, 02:44 PM
DaveZ
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
WebFOCUS 7.7.1 Windows 2000 Output: Excel and PDF
August 30, 2011, 03:32 PM
Tom Flynn
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.
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
WebFOCUS 7.7.1 Windows 2000 Output: Excel and PDF
September 08, 2011, 04:05 PM
DaveZ
Thanks to every one for all the help, I ended up using MATCH and it worked perfectly
WebFOCUS 7.7.1 Windows 2000 Output: Excel and PDF
September 08, 2011, 04:34 PM
susannah
did you ever try ON TABLE HOLD AS ... FORMAT XFOCUS as JGelona suggested?
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID