Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Unanswered: Left join exchange rate table


    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!
    Last edited by tosa; 10-21-11 at 03:10.

  2. #2
    Join Date
    Oct 2011
    Can you break the query into several queries, and store your temp results into a temp table?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts