dotConnect for Salesforce can translate a subset of SQL-92 SELECT statements to SOQL for remote execution. As remote execution provides much higher performance than local one, it makes sense to use SELECT statements that are translatable to SOQL.

The following syntax diagrams demonstrate the syntax of SELECT statements that can be converted to SOQL and executed remotely:

SELECT:

column:

result-column:

table:

join:

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

• The SELECT clause of the SELECT statement must contain only column names or "*" or aggregate functions. If you use any other SQL functions or expressions, the query will not be translatable.

• The FROM clause must contain only one table or joined tables. JOIN can be translated only if all of the following conditions are met:

  • Joined tables correspond to the Salesforce.com or Database.com objects that are in relation to each other.
  • Join is performed on the relationship field (column) and the ID field (column).
  • "Child" object (table) is placed first in the JOIN.

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

• WHERE clause must contain only comparisons of a column and a constant or the LIKE operator. SOQL also requires that column was on the left side of the comparisons, but this is not necessary with dotConnect for Salesforce. Comparisons are united with the logical operators AND and OR.
• ORDER BY and GROUP BY clauses are supported, but only sorting and grouping on fields (columns) are available. Expressions are not allowed.