Hello,

Can anyone help me with this error? I created a fex file that would show summary and detail reports in different tabs...the summary report would use a JOIN on hold files I created. The report I created using the JOIN runs correctly and the detail report also runs when I run both reports separately...However, I get the error below when I do a compound report on these...

-* File ship_performance_tracking.fex
ENGINE SQLSYB SET DEFAULT_CONNECTION SYBASE
SQL SQLSYB
EX mastersys..DetentionByShipper_Winnie 'GMMIMN','GEMQMN', 'GEMUMN', '20120101', '20120108','Y','N'
;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DETENTIONBYWEEK
END

SQL SQLSYB
EX mastersys..DetentionByShipper_Winnie 'GMMIMN','GEMQMN', 'GEMUMN', '20111127', '20120108','Y','N'
;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DETENTIONSIXWEEKS
END

TABLE FILE DETENTIONBYWEEK
-*BY TOTAL HIGHEST 5 over2Hrs NOPRINT
SUM
realTime
over2Hrs AS 'SUMOVER2HRS'
COMPUTE AVEOVER2HRS/D12.2 = AVE.over2Hrs;
BY LOWEST puCity
BY puState
ON TABLE SUBHEAD
"Live Loading Detail"
WHERE over2Hrs GT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS WEEKLYSUMMARY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=TABHEADING,
LINE=1,
JUSTIFY=LEFT,
$
ENDSTYLE
END
-*COUNT LDS LESS 2 HRS
TABLE FILE DETENTIONBYWEEK
SUM
puState
COMPUTE CNTLESS2HRS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
WHERE over2Hrs LT 2;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TOTALLESS2HRS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-*COUNT TOTAL LDS
TABLE FILE DETENTIONBYWEEK
SUM
puState
COMPUTE CNTLDS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TOTALOVER2HRS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS AVE ON HRS AND LOADS OVER 2 HRS
TABLE FILE DETENTIONSIXWEEKS
SUM
over2Hrs
COMPUTE SIXWKAVGOVER2HRS/D12.2 = AVE.over2Hrs;
COMPUTE CNTLDSOVER2HRS/D12.2 = CNT.over2Hrs / 6;
BY LOWEST puCity
BY LOWEST puState
WHERE over2Hrs GT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKAVGSUMMARY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS CNT OF LESS 2 HRS BY CITY/ST
TABLE FILE DETENTIONSIXWEEKS
SUM
COMPUTE SIXWKCNTLESS2HRS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY LOWEST puState
WHERE over2Hrs LT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKTOTALLESS2HRS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS CNT OF ALL LOADS BY CITY/ST
TABLE FILE DETENTIONSIXWEEKS
SUM
COMPUTE SIXWKLDCNT/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY LOWEST puState
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKTOTALLDS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
JOIN
SIXWKAVGSUMMARY.SIXWKAVG.puCity IN SIXWKAVGSUMMARY TO MULTIPLE
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity IN SIXWKTOTALLESS2HRS TAG J3 AS J3
END
JOIN
SIXWKAVGSUMMARY.SIXWKAVG.puCity IN SIXWKAVGSUMMARY TO MULTIPLE
SIXWKTOTALLDS.SIXWKTOT.puCity IN SIXWKTOTALLDS TAG J4 AS J4
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
TOTALOVER2HRS.TOTALOVE.puCity IN TOTALOVER2HRS TAG J5 AS J5
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKAVGSUMMARY.SIXWKAVG.puCity IN SIXWKAVGSUMMARY TAG J6 AS J6
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity IN SIXWKTOTALLESS2HRS TAG J7 AS J7
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKTOTALLDS.SIXWKTOT.puCity IN SIXWKTOTALLDS TAG J8 AS J8
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
TOTALLESS2HRS.TOTALLES.puCity IN TOTALLESS2HRS TAG J0 AS J0
END
TABLE FILE WEEKLYSUMMARY
PRINT
puCity
puState
over2Hrs
AVEOVER2HRS
COMPUTE PCTLDIN2HRS/D12% = ( CNTLESS2HRS / CNTLDS ) * 100;
SIXWKAVGOVER2HRS
CNTLDSOVER2HRS
COMPUTE SIXWKLDIN2HRS/D12% = ( SIXWKCNTLESS2HRS / SIXWKLDCNT ) * 100;
BY HIGHEST 5 realTime NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
JOIN CLEAR *
TABLE FILE DETENTIONBYWEEK
PRINT
proNumber
billOfLading
shipperName
puName
puCity
puState
delName
delCity
delState
hoursBucket
schedDate
actualDate
releasedDate
realTime
over2Hrs
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AS EXL2K CLOSE
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N7,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
ENDSTYLE
END


-***ERROR


0 NUMBER OF RECORDS IN TABLE= 192 LINES= 192
1
0 NUMBER OF RECORDS IN TABLE= 1201 LINES= 1201
0
0 NUMBER OF RECORDS IN TABLE= 42 LINES= 17
0 NUMBER OF RECORDS IN TABLE= 181 LINES= 32
0 NUMBER OF RECORDS IN TABLE= 192 LINES= 32
0 NUMBER OF RECORDS IN TABLE= 235 LINES= 37
0 NUMBER OF RECORDS IN TABLE= 951 LINES= 60
0 NUMBER OF RECORDS IN TABLE= 1201 LINES= 70
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : TOTALLESS2HRS/LOUISVILLE
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : SIXWKTOTALLDS/LOUISVILLE
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : SIXWKTOTALLESS2HRS/LOUISVILLE
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : SIXWKAVGSUMMARY/LOUISVILLE
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : TOTALOVER2HRS/LOUISVILLE
1
0 NUMBER OF RECORDS IN TABLE= 86 LINES= 5
(FOC3298) ERROR FOUND IN A COMPOUND REPORT
Compound Report is TERMINATING.....


I appreciate any help. Thanks!

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

In few hold files you are using BY puState and in few it is BY LOWEST puState, The other thing is you are not using the SET COMPOUND command and the third one is there is not OPEN for the first PCHOLD but you used CLOSE for the next one.

I see where you are closing the compound report but I don't see where you are opening it on your first TABLE FILE request of the compound report. Try adding "OPEN" to your first one after "EXL2K" (ON TABLE PCHOLD FORMAT EXL2K OPEN) just as you have "CLOSE" on the second one.

Thanks! I did have "OPEN" on the first report but i couldn't run the report separately with that command when i was testing it so i removed it and forgot to put it back there...

Anyway, I did take out the "BY LOWEST" on the puState and added the SET COMPOUND, but I still got the same result...It's weird that I'm able to run both reports separately but not together and I've done compound reports quite a few times before so I'm thinking it's something with the JOIN on the HOLD tables...

if you want to join hold files you should hold them format FOCUS and an INDEX field to join.

you have to solve this first
then add

-SET &ECHO=ALL;

at start then you get more and better error messages
put an -EXIT after the JOIN part

and I wonder why you JOIN you do not use the joined tables

Thanks, Frank! I added INDEX on my hold files and added the -SET &ECHO command but i'm getting a different error now. Something to do with the files not having Key field or segment...I basically joined the HOLD files together using puCity and puState as the keys. I checked and these are all the same. The report I created using the JOIN runs fine if i run it separately but it gets an error when I do a compound report.

(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:


TABLE FILE DETENTIONBYWEEK
-*BY TOTAL HIGHEST 5 over2Hrs NOPRINT
SUM
realTime
over2Hrs AS 'SUMOVER2HRS'
COMPUTE AVEOVER2HRS/D12.2 = AVE.over2Hrs;
BY LOWEST puCity
BY puState
ON TABLE SUBHEAD
"Live Loading Detail"
WHERE over2Hrs GT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS WEEKLYSUMMARY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=TABHEADING,
LINE=1,
JUSTIFY=LEFT,
$
ENDSTYLE
END
-*COUNT LDS LESS 2 HRS
TABLE FILE DETENTIONBYWEEK
SUM
COMPUTE CNTLESS2HRS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
WHERE over2Hrs LT 2;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TOTALLESS2HRS FORMAT FOCUS INDEX puCity
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-*COUNT TOTAL LDS
TABLE FILE DETENTIONBYWEEK
SUM
COMPUTE CNTLDS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS TOTALOVER2HRS FORMAT FOCUS INDEX puCity
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS AVE ON HRS AND LOADS OVER 2 HRS
TABLE FILE DETENTIONSIXWEEKS
SUM
over2Hrs
COMPUTE SIXWKAVGOVER2HRS/D12.2 = AVE.over2Hrs;
COMPUTE CNTLDSOVER2HRS/D12.2 = CNT.over2Hrs / 6;
BY LOWEST puCity
BY puState
WHERE over2Hrs GT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKAVGSUMMARY FORMAT FOCUS INDEX puCity
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS CNT OF LESS 2 HRS BY CITY/ST
TABLE FILE DETENTIONSIXWEEKS
SUM
COMPUTE SIXWKCNTLESS2HRS/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
WHERE over2Hrs LT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKTOTALLESS2HRS FORMAT FOCUS INDEX puCity
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-* 6 WEEKS CNT OF ALL LOADS BY CITY/ST
TABLE FILE DETENTIONSIXWEEKS
SUM
COMPUTE SIXWKLDCNT/D12.2 = CNT.over2Hrs;
BY LOWEST puCity
BY puState
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS SIXWKTOTALLDS FORMAT FOCUS INDEX puCity
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
-*JOIN HOLD FILES TO GET SUMMARY FOR LOADING
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity IN SIXWKTOTALLESS2HRS TAG J7 AS J7
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKTOTALLDS.SIXWKTOT.puCity IN SIXWKTOTALLDS TAG J8 AS J8
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
TOTALLESS2HRS.TOTALLES.puCity IN TOTALLESS2HRS TAG J1 AS J1
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
TOTALOVER2HRS.TOTALOVE.puCity IN TOTALOVER2HRS TAG J2 AS J2
END
JOIN
WEEKLYSUMMARY.WEEKLYSU.puCity IN WEEKLYSUMMARY TO MULTIPLE
SIXWKAVGSUMMARY.SIXWKAVG.puCity IN SIXWKAVGSUMMARY TAG J9 AS J9
END
-*SUMMARY REPORT FROM JOIN
SET COMPOUND = OPEN
TABLE FILE WEEKLYSUMMARY
PRINT
puCity AS 'Target Live Loading Facilities'
puState AS 'State'
over2Hrs AS 'Last Week,Sum of ,Hrs > 2 Hrs'
AVEOVER2HRS AS 'Last Week,Avg Hrs on,Lds > 2 Hrs'
COMPUTE PCTLOADIN2HRS/D12% = ( CNTLESS2HRS / CNTLDS ) * 100; AS 'Last Week,% Lds,w/in 2 Hrs'
SIXWKAVGOVER2HRS AS '6 wk Avg,of Hrs > 2,Hrs'
CNTLDSOVER2HRS AS '6 wk Avg,on Lds > 2,Hrs'
COMPUTE SIXWKLDIN2HRS/D12% = ( SIXWKCNTLESS2HRS / SIXWKLDCNT ) * 100; AS '6 wk % Lds,w/in 2 Hrs'
BY HIGHEST 5 realTime NOPRINT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K OPEN
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=DATA,
COLUMN=N2,
SIZE=8,
$
TYPE=DATA,
COLUMN=N3,
SIZE=8,
$
TYPE=DATA,
COLUMN=N4,
SIZE=8,
$
TYPE=DATA,
COLUMN=N5,
SIZE=8,
$
TYPE=DATA,
COLUMN=N6,
SIZE=8,
$
TYPE=DATA,
COLUMN=N7,
SIZE=8,
$
TYPE=DATA,
COLUMN=N8,
SIZE=8,
$
TYPE=DATA,
COLUMN=N9,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N2,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N3,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N4,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N5,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N6,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N7,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N8,
SIZE=8,
$
TYPE=TITLE,
COLUMN=N9,
SIZE=8,
$
ENDSTYLE
END
-EXIT
0 NUMBER OF RECORDS IN TABLE= 192 LINES= 192
1
0 NUMBER OF RECORDS IN TABLE= 1201 LINES= 1201
0
0 NUMBER OF RECORDS IN TABLE= 42 LINES= 17
0 NUMBER OF RECORDS IN TABLE= 181 LINES= 32
0 NUMBER OF RECORDS IN TABLE= 192 LINES= 32
0 NUMBER OF RECORDS IN TABLE= 235 LINES= 37
0 NUMBER OF RECORDS IN TABLE= 951 LINES= 60
0 NUMBER OF RECORDS IN TABLE= 1201 LINES= 70
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity
(FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT:
SIXWKTOTALLESS2HRS.SIXWKTOT.puCity
BYPASSING TO END OF COMMAND

I fixed my issue.

I had to use "hold format focus index" with all the files.

This is because with the FOCUS format the fields format is not the same.