How to Query External Object in Salesforce?

/ apex, Apex Code Practice, Salesforce / By
Introduction

Query on external object is supported with some limitation. Here is Salesforce help article: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_limits.htm

Sample Query

SELECT Id, Name__c, OrderNumber__c FROM ExternalOrder__x WHERE Name__c LIKE ‘%Software%’

Additional Notes
  • External SOQL does not support Long Text Area for queries search, Text is supported on the other hand.
  • In SOQL queries of external objects, IN clauses with more than approximately 15 IDs return the error “This operation is too complicated for Secure Agent.” The exact IN clause limit varies based on SharePoint ID length.
  • A subquery that involves external objects can fetch up to 1,000 rows of data.
  • Each SOQL query can have up to 4 joins across external objects and other types of objects.Each join requires a separate round trip to the external system when executing the query. Expect longer response times for each join in a query.
  • External objects don’t support the following aggregate functions and clauses.
    • AVG() function
    • COUNT(fieldName) function (however, COUNT() is supported)
    • HAVING clause
    • GROUP BY clause
    • MAX() function
    • MIN() function
    • SUM() function
  • External objects also don’t support the following.
    • EXCLUDES operator
    • FOR VIEW clause
    • FOR REFERENCE clause
    • INCLUDES operator
    • LIKE operator
    • toLabel() function
    • TYPEOF clause
    • WITH clause
  • The following limits apply only to the OData 2.0 and 4.0 adapters for Salesforce Connect.
  • External objects have the following limitations for the ORDER BY clause.
    • NULLS FIRST and NULLS LAST are ignored.
    • External objects don’t support the ORDER BY clause in relationship queries.
  • The COUNT() aggregate function is supported only on external objects whose external data sources have Request Row Counts enabled. Specifically, the response from the external system must include the total row count of the result set.
  • The following limits apply only to custom adapters for Salesforce Connect.
  • Location-based SOQL queries of external objects aren’t supported.
  • If a SOQL query of an external object includes the following, the query fails.
    • convertCurrency() function
    • UPDATE TRACKING clause
    • UPDATE VIEWSTAT clause
    • USING SCOPE clause
  • In an ORDER BY clause, the following are ignored.
    • NULLS FIRST syntax
    • NULLS LAST syntax
  • In Apex tests, use dynamic SOQL to query external objects. Tests that perform static SOQL queries of external objects fail.

Related Posts