Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Question Unanswered: display the cost in US currency

    Hi,

    I am making a query showing the cost (cost) in US currency. However, the transaction maybe of different currency (currency_code). I would like to ask how to write the query to calculate the cost and show all the transaction with cost > US10000

    here is the query i would like to make

    select <if currency_code = 1, cost = value * 3, if currency_code = 2, cost = value * 4) from table a where
    cost > 10000

    Thank you for your help.
    Last edited by thtang; 10-13-11 at 03:48.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by thtang View Post
    select <if currency_code = 1, cost = value * 3, if currency_code = 2, cost = value * 4) from table a where
    cost > 10000
    Maybe before any code attempts, you should study the syntax. For Oracle SQL, it is described in SQL Reference book, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
    Open the one for your Oracle version and search for CASE expression or DECODE function. SELECT statement will be useful too.

    Just curious: is the rate supposed to be fixed for all query lifetime?

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends where you are getting your foreign exchange values from.
    if you need uptodate values you can find providers on the web, otherwise you need a local table.

    Personally I'd use a local table, insert all the various currencies you support with a forex value. in this case, say, thats gong to be CDN, 1.01926, SFR 0.89590, GBP 0.63570...
    but also define your base currency in this case USD with a conversion factor of 1. that way round you treat all calculations the same.

    bear in mind that currency conversions are a potential minefield. you need to have a thorough understanding of how the information is consumed. its often a requirement to store the exchange rate + the date and time of that rate as part of the transaction.

    also some consumers of information demand a specific level of precision. ferinstance in financial reporting you need to treat all times consistently. the sum should reflect the sum of the individual items converted as the prevailing rate. and that calcualtion must be consistent across the system. so if you only refer to the sub total in other reports / forms etc you must make certain the sub total is the sum of the items coverted, not the sum converted
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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