Force.com SOQL and SOSL Reference

Introduction to SOQL and SOSL
Salesforce Object Query Language (SOQL)
Typographical Conventions in This Document
Quoted String Escape Sequences
Reserved Characters
Alias Notation
SOQL SELECT Syntax
Condition Expression Syntax (WHERE Clause)
fieldExpression Syntax
USING SCOPE
ORDER BY
LIMIT
OFFSET
Update an Article’s Keyword Tracking with SOQL
Update an Article Viewstat with SOQL
WITH filteringExpression
GROUP BY
HAVING
TYPEOF
FORMAT ()
FOR VIEW
FOR REFERENCE
FOR UPDATE
Aggregate Functions
Date Functions
Querying Currency Fields in Multi-currency Orgs
Example SELECT Clauses
Relationship Queries
Change the Batch Size in Queries
SOQL Limits on Objects
Syndication Feed SOQL and Mapping Syntax
Location-Based SOQL Queries
Salesforce Object Search Language (SOSL)
PDF

Newer Version Available

This content describes an older version of this product. View Latest

Querying Currency Fields in Multi-currency Orgs

Use convertCurrency() in the SELECT statement of a SOQL query to convert currency fields to the user’s currency. This action requires that the org has multiple currencies enabled.

The following syntax is for using convertCurrency() with the SELECT clause:

For example:

Use an ISO code that your org has enabled and made active. If you don’t put in an ISO code, the numeric value is used instead of comparative amounts. Using the previous example, opportunity records with JPY5001, EUR5001, and USD5001 would be returned. If you use IN in a WHERE clause, you can’t mix ISO code and non-ISO code values.

To format currencies according to the user’s local, use FORMAT() with SELECT() statements. In this example, convertedCurrency is an alias for the returned amount, which is formatted appropriately in the user interface.

If an org has enabled advanced currency management, dated exchange rates are used when converting currency fields on opportunities, opportunity line items, and opportunity history. With advanced currency management, convertCurrency uses the conversion rate that corresponds to a given field (for example, CloseDate on opportunities). When advanced currency management isn’t enabled, the most recent conversion date entered is used.

Considerations and Workarounds

You can’t 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 org.

For example:

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

You can’t convert the result of an aggregate function into the user’s currency by calling the convertCurrency() function. If a query includes a GROUP BY or HAVING clause, currency data returned by using an aggregate function, such as SUM() or MAX(), is in the org’s default currency.

For example:

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’t work.

You can’t use convertCurrency() with ORDER BY. Ordering is always based on the converted currency value, just like in reports.