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:
- 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.