Querying Currency Fields in Multicurrency Organizations

If an organization is multicurrency enabled, you can use convertCurrency() in the SELECT clause to convert currency fields to the user's currency.

Use this syntax for the SELECT clause:

convertCurrency(field)

For example:

SELECT Id, convertCurrency(AnnualRevenue)
FROM Account

If an organization has enabled advanced currency management, dated exchange rates will be used when converting currency fields on opportunities, opportunity line items, and opportunity history.

You cannot use the convertCurrency() function in a WHERE clause. If you do, an error is returned. Use the following syntax to convert a numeric value to the user's currency, from any active currency in your organization:

WHERE Object_name Operator ISO_CODEvalue

For example:

SELECT Id, Name
FROM Opportunity
WHERE Amount > USD5000

In this example, opportunity records will be returned if the record's currency Amount value is greater than the equivalent of USD5000. For example, an opportunity with an amount of USD5001 would be returned, but not JPY7000.

Use an ISO code that your organization has enabled and is active. If you do not put in an ISO code, then the numeric value is used instead of comparative amounts. Using the example above, opportunity records with JPY5001, EUR5001, and USD5001 would be returned. Note that if you use IN in a WHERE clause, you cannot mix ISO code and non-ISO code values.

Note

Ordering is always based on the converted currency value, just like in reports. Thus, convertCurrency() cannot be used with ORDER BY.

If a query includes a GROUP BY or HAVING clause, any currency data returned by using an aggregate function, such as SUM() or MAX(), is in the organization's default currency. You cannot convert the result of an aggregate function into the user's currency by calling theconvertCurrency() function.

For example:

SELECT Name, MAX(Amount)
FROM Opportunity
GROUP BY Name
HAVING MAX(Amount) > 10000

You can't use ISO_CODEvalue to represent a value in a particular currency, such as USD, when you use an aggregate function. For example, the following query does not work:

SELECT Name, MAX(Amount)
FROM Opportunity
GROUP BY Name
HAVING MAX(Amount) > USD10000