I am getting an error message once I create a hold file out of a report and try to do a simple query on a sum value.
When I display the HOLD2 as HTML without the query I get no errors. Once I put it into a hold file and try to do a where E_Price is GE 5000 I get the join duplicates errors. I have posted the code below.

  
-DEFAULT &BEGDATE='11/18/2011'
-DEFAULT &ENDDATE='12/1/2011'
SET ALL=ON
SET KEEPDEFINES=ON

TABLE FILE F4211
PRINT
COMPUTE E_PRICE/D12.2= F4211.F4211.EXTENDED_PRICE;
     F4211.F4211.ORDER_NUMBER
	 F4211.F4211.DESCRIPTION
     F4211.F4211.ORDER_TYPE_CODE
     F4211.F4211.ORDER_DATE
     F4211.F4211.LEGACY_PART_NUMBER
	 F4211.F4211.EXTENDED_PRICE
BY  LOWEST F4211.F4211.SHIP_TO
BY  LOWEST F4211.F4211.ITEM_NUMBER__SHORT
WHERE ( F4211.F4211.ORDER_DATE GE '&BEGDATE' ) AND ( F4211.F4211.ORDER_DATE LE '&ENDDATE' );
WHERE F4211.F4211.ORDER_TYPE_CODE EQ 'SQ';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

TABLE FILE F4211
PRINT
     F4211.F4211.ORDER_NUMBER
     F4211.F4211.DESCRIPTION
     F4211.F4211.ORDER_TYPE_CODE
     F4211.F4211.ORDER_DATE
     F4211.F4211.LEGACY_PART_NUMBER
     F4211.F4211.EXTENDED_PRICE
BY  LOWEST F4211.F4211.SHIP_TO
BY  LOWEST F4211.F4211.ITEM_NUMBER__SHORT
WHERE ( F4211.F4211.ORDER_DATE GE '&BEGDATE' );
WHERE F4211.F4211.ORDER_TYPE_CODE EQ 'SP' OR 'SY' OR 'S3';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

JOIN
 HOLD1.HOLD1.SHIP_TO AND HOLD1.HOLD1.ITEM_NUMBER__SHORT IN HOLD1 TO MULTIPLE HOLD.HOLD.SHIP_TO AND HOLD.HOLD.ITEM_NUMBER__SHORT IN HOLD
  TAG J0 AS J0
 END

TABLE FILE HOLD1
SUM
     HOLD1.HOLD1.E_PRICE
BY  LOWEST HOLD1.HOLD1.SHIP_TO
BY  LOWEST HOLD1.HOLD1.ORDER_NUMBER NOPRINT
PRINT
     HOLD1.HOLD1.ITEM_NUMBER__SHORT
     HOLD1.HOLD1.DESCRIPTION
     HOLD1.HOLD1.ORDER_DATE AS 'QuoteDate'
     HOLD1.HOLD1.ORDER_NUMBER AS 'QuoteNumber'
     J0.HOLD.ORDER_NUMBER AS 'OrderNumber'
     J0.HOLD.ORDER_TYPE_CODE AS 'OrderType'
     J0.HOLD.ORDER_DATE AS 'OrderDate'
     J0.HOLD.EXTENDED_PRICE/P16.2CSN AS 'OrderPrice'
     HOLD1.HOLD1.EXTENDED_PRICE/P16.2CSN
BY  LOWEST HOLD1.HOLD1.SHIP_TO
BY  LOWEST HOLD1.HOLD1.ORDER_NUMBER NOPRINT

ON HOLD1.HOLD1.SHIP_TO SUBTOTAL
     'J0.HOLD.EXTENDED_PRICE'
     'HOLD1.HOLD1.EXTENDED_PRICE' MULTILINES AS '*TOTAL'
WHERE HOLD1.HOLD1.EXTENDED_PRICE GT 0;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA,
     COLUMN=N2,
     COLOR='BLACK',
$
TYPE=TITLE,
     COLUMN=N2,
     COLOR='BLACK',
$
ENDSTYLE
END
TABLE FILE HOLD2
PRINT
     HOLD2.HOLD2.ITEM_NUMBER__SHORT
     HOLD2.HOLD2.DESCRIPTION
     HOLD2.HOLD2.QuoteDate AS 'Quote Date'
     HOLD2.HOLD2.QuoteNumber AS 'Quote Number'
     HOLD2.HOLD2.OrderNumber AS 'Order Number'
     HOLD2.HOLD2.OrderType AS 'Order Type'
     HOLD2.HOLD2.OrderDate AS 'Order Date'
     HOLD2.HOLD2.OrderPrice/P16.2CSN AS 'Order Price'
     HOLD2.HOLD2.EXTENDED_PRICE/P16.2CSN AS 'Quoted Price'
BY  LOWEST HOLD2.HOLD2.SHIP_TO
BY  LOWEST HOLD2.HOLD2.QuoteNumber NOPRINT

ON HOLD2.HOLD2.SHIP_TO SUBTOTAL
     'HOLD2.HOLD2.OrderPrice'
     'HOLD2.HOLD2.EXTENDED_PRICE' MULTILINES AS '*TOTAL'
WHERE HOLD2.HOLD2.E_PRICE GE 5000;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL' 'HOLD2.HOLD2.OrderPrice' 'HOLD2.HOLD2.EXTENDED_PRICE'
ON TABLE PCHOLD AS HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA,
     COLUMN=N2,
     COLOR='BLACK',
$
TYPE=TITLE,
     COLUMN=N2,
     COLOR='BLACK',
$
ENDSTYLE
END

This is the error message:
0 NUMBER OF RECORDS IN TABLE= 3765 LINES= 3765
0 NUMBER OF RECORDS IN TABLE= 17591 LINES= 17591
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : HOLD/ 300272 10022118
.
.
.
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD : HOLD/ 1217355 30349032
1
0 NUMBER OF RECORDS IN TABLE= 4533 LINES= 4533
0 ERROR AT OR NEAR LINE 100 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: HOLD2.HOLD2.QuoteDate
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Any help is greatly appreciated.

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

I humbly recommend you place the following line in each request:

ON TABLE SET ASNAMES ON


. . . and then give each field in each request unique names via AS clauses:

BY LOWEST F4211.F4211.ITEM_NUMBER__SHORT AS HOLD1_ITEM_NUMBER__SHORT


That way you can discern between HOLD1_ITEM_NUMBER__SHORT and HOLD2_ITEM_NUMBER__SHORT in the joined structure. This will make the compiler's job easier. It will likely make your job easier during test/debug, and a year from now when you need to revise the code.

J.

That sounds like a good idea. I will do that. I usually don't have to pull from the same table twice like in this report.
Thanks,

Joining two alpha formatted files wont work

you should hold FORMAT FOCUS with an INDEX field in the child TABLE you create.

I always have the following settings

SET ASSNAMES=ON
SET HOLDIST=PRINTONLY

quote:

SET ASSNAMES=ON

No pun intended, I hope

quote:

ASNAMES and you should hold FORMAT FOCUS with an INDEX field in the child TABLE you create

Should do the trick.

quote:

Originally posted by Doug:

quote:

ASNAMES and you should hold FORMAT FOCUS with an INDEX field in the child TABLE you create

Should do the trick.

Thanks for all the suggestions. I rebuilt all my hold tables with the asnames and tried the format focus with index but was getting other errors.
I looked up joining alphas and if they have the same sequence the join will work. So I change them back to alphas making sure that all hold files had the same BY values and it worked.
Thanks to everyone who contributed.

Someone should have mentioned that JOIN A AND B IN T1 TO A AND B IN T2 will not work for FOCUS databases. I've heard rumours that it works for XFOCUS databases but I haven't verified that yet.