I have the following code in my report:

-DEFAULT &FROM_DATE = '20110207';
-DEFAULT &TO_DATE   = '20110209';
-DEFAULT &VENDOR# = 'FOC_NONE'

DEFINE FILE AE_DT1
	VENDOR_NUMBER/I9 = EDIT(AE_DT1.AE_DT1.FIELD1) ;
    INVOICE_NUMBER/A25 = EDIT(AE_DT1.AE_DT1.FIELD2, '9999999999999999999999999') ;
	SCAN_DATE/A8YYMD = EDIT(AE_DT1.AE_DT1.FIELD24, '9999$99$99') ;
END

TABLE FILE AE_DT1
PRINT
	AE_DT1.AE_DT1.FIELD5 AS 'INVOICE_DATE'
	AE_DT1.AE_DT1.FIELD6 AS 'AMOUNT'
	SCAN_DATE
WHERE SCAN_DATE GE '20110201';
WHERE VENDOR_NUMBER EQ '8138864'
BY VENDOR_NUMBER
BY INVOICE_NUMBER
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS TBL1
END
-RUN

DEFINE FILE F0411
BATCH_DATE/A8YYMD = BATCH_DATE ;
G_L_DATE/A8YYMD = G_L_DATE ;
END

TABLE FILE F0411
PRINT
	INVOICE_DATE
	GROSS_AMOUNT
	G_L_DATE
	PAYMENT_TERMS
WHERE G_L_DATE NE '';
WHERE BATCH_DATE NE '';
WHERE G_L_DATE GE '&FROM_DATE.From GL Date.';
WHERE G_L_DATE LE '&TO_DATE.Through GL Date.';
WHERE ADDRESS_NUMBER EQ '8138864';
BY ADDRESS_NUMBER
BY INVOICE_NUMBER
ON TABLE HOLD AS TBL2
END
-RUN

JOIN INNER VENDOR_NUMBER AND INVOICE_NUMBER IN TBL1
   TO ADDRESS_NUMBER AND INVOICE_NUMBER IN TBL2 TAG J001
AS J001
END

DEFINE FILE TBL1
DIFF1/YYMD = DATECVT(SCAN_DATE,'A8YYMD','YYMD');
DIFF2/YYMD = DATECVT(G_L_DATE,'A8YYMD','YYMD');
DIFF3/D20 = DIFF2 - DIFF1;
END

TABLE FILE TBL1
PRINT
ALPHA_NAME AS 'VENDOR NAME'
VENDOR_NUMBER AS 'Vendor Number'
INVOICE_NUMBER AS 'Invoice Number'
INVOICE_DATE AS 'Invoice Date'
AMOUNT AS 'Amount'
SCAN_DATE AS 'Scan Date'
J001.TBL2.G_L_DATE AS 'G_L_Date'
DIFF3 AS '# Days b/w Scan and G_L Date'
J001.TBL2.PAYMENT_TERMS AS 'Payment Terms'
WHERE J001.TBL2.G_L_DATE NE ''
ON TABLE PCHOLD FORMAT EXL2K
END

It returns the data fine. But when I add another join, it only gets the first record from the new table and attach it to the other records, it looks like it does not recognize the join.

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

If it would not recognize the join, then it would also not retrieve just one record fromthe new table.
Did you specify the join as a multiple join (JOIN ... TO ALL ...)?

Can you also post the extra join and the format of the file or the code that generated it ?

I was able to get this resolved. Issue had to do with teh field format. Once had the format P8 and the other had I9. Once we got the format the same. We got correct data from the join.