SQL translation

The driver translates a subset of SQL-92 SELECT statements into SOQL for remote execution. UPDATE, INSERT, and DELETE statements are mapped to corresponding Salesforce API calls for data modification.

To be translatable, a SELECT statement must meet the requirements described on this page.

Note

Remote execution provides significantly higher performance than local execution. It’s recommended to use SELECT statements that are translatable into SOQL. For more information, see Query processing.

Syntax overview

The following diagrams show the SELECT statement syntax that are translated into SOQL and executed remotely.

select:

column:

result-column:

table:

join:

Requirements

To be translatable into SOQL, a SELECT statement must meet the following requirements:

• SELECT clause: It must include only column names, an asterisk (*), or aggregate functions. SQL functions or expressions are not translatable.

• FROM clause: It must contain only one table or joined tables. A JOIN is translatable only if all of the following conditions are met:

  • Joined tables correspond to the Salesforce objects that are linked through object relationships.
  • The JOIN is performed on the relationship field (column) and the ID field (column).
  • Child object (table) is placed first in the JOIN.

  Two types of JOIN can be translated into SOQL: LEFT JOIN (or LEFT OUTER JOIN) and INNER JOIN. INNER JOIN can be used multiple times to connect more than two tables. However, if you use LEFT JOIN, it must be the only JOIN in the statement.

• The WHERE clause must contain only comparisons of a column and a constant or the LIKE operator. Although SOQL requires the column to be on the left side of comparison expressions, this restriction does not apply when using Devart ODBC Driver for Salesforce. Comparisons are united with the logical operators AND and OR.
• The ORDER BY and GROUP BY clauses are supported, but only sorting and grouping on fields (columns) are available. Expressions are not allowed.