Hi!

Once again I'm stuck and in need of help from you pros. I'm trying to get the correct exchange rate for each row in a certain table. The db structure looks like this:

tblPayment p
- pmtNum
- currency
- valueDate
- paymentDate

tblPaymentLine pl
- pmtNum
- itemcode
- price

tblExcRate ex
- excRate
- toCurrency
- beginDate
- excRateKey (<- don't know if this is needed)

What I want to select is all rows from tblPaymentLine between certain dates and calculated to a certain currency. The select should go something like "SELECT p.paymentDate, pl.itemcode, CASE p.currency WHEN 'EUR' THEN pl.price ELSE (pl.price*ex.excRate) END AS 'price'". What I can't seem to do though, is left join the currency table. The date for the correct exchange rate would be, as a logical statement: "MAX(ex.beginDate) WHERE ex.beginDate <= p.valueDate and ex.toCurrency=p.currency".

So how do I get this into a complete SQL sentence? It's a Progress OpenEdge database. The query doesn't need to worry about if p.valueDate < ex.beginDate.

Thank you!

Can you break the query into several queries, and store your temp results into a temp table?