Hi All:

I'm having trouble getting the desired results for an SQL statement.

I have 2 tables. FEEDINV has a bunch of records of invoices, each with a delivery date. For each record in FEEDINV, I want a matching record in RATPRIC, where the date in RATPRIC is equal to or less than, BUT I only want the CLOSEST record in RATPRIC AND only if is within 7 days. (Kind of like SET NEAR ON, SEEK). ALSO, I only want a match if FEEDINV.fi_cpprim = RATPRIC.rp_cpprim.

The attached code doesn't work because:

1) It will give me ALL records in RATPRIC that are within 7 days and I only want the CLOSEST.
2) For some reason, it doesn't give me FEEDINV records if the cpprim field cannot be found in RATPRIC. I thought that it shoud give you NULL values if there is no match on a RIGHT OUTER JOIN.

Thanks,

Yossi

SET DELETED on

SELECT fi_flcode, fi_cpprim, fi_weight, fi_dlvdat, rp_rtprim, rp_date, rp_cpprim ;
FROM feedinv ;
left OUTER JOIN ratpric ;
ON fi_rtprim = rp_rtprim ;
AND fi_cpprim = rp_cpprim ;
AND fi_dlvdat > rp_date ;
WHERE fi_flcode = 'BAA12062'  ;
AND fi_dlvdat - rp_date < 7