dotConnect for Zoho CRM can translate a subset of SQL-92 SELECT statements to the corresponding Zoho CRM API calls for remote execution. As remote execution provides much higher performance than local one, it makes sense to use SELECT statements that are translatable to API Calls.

SELECT statements that correspond to the following pattern and conditions can be converted to API calls and executed remotely:

SELECT result_columns FROM table [WHERE where_condition] [ORDER BY orderByClause] 

• result_columns can be a list of the columns, separated with commas, or "*", which means all the result columns.
• table - the name of the Zoho CRM object.
• where_condition - one condition. Condition must be a comparison of a column of the selected object and a constant or a parameter. No arithmetic or other functions are allowed. The following comparison operators are supported: =, <=, >=, !=, <, >, IN, LIKE, BETWEEN, IS NULL, IS NOT NULL. Only the columns that allows filtering can be included in where_condition
• orderByClause - one column name optionally followed by "ASC" or "DESC".

If a SELECT statement contains any of the features, not explicitly mentioned above, it cannot be translated to Zoho CRM API calls and by default, it is executed locally, which usually takes more time and resources than remote execution. (Local execution can be disabled completely if necessary - see Select Statement Execution).

Exceptions

If a statement contains any of the exceptions, listed below, it also cannot be translated to the Zoho CRM API calls and is executed locally.

• A SELECT statement, having both ORDER BY and WHERE clauses cannot be converted to API calls.
• WHERE clause for the Calls object.
• IN operator in the WHERE clause can be converted to API calls only when it contains up to 5 values.
• ORDER BY clause, containg a primary key or foreign key column cannot be converted to API calls.

Filterable Columns and Allowed Comparison Operators

For primary key fields, only the "=" operator is supported in the WHERE clause. If you use other operators for them, the query will be executed locally. Any of the supported comparison operators used in the WHERE clause with other columns except for the Calls object columns can be translated to API calls.