Read data using COQL Query Builder

Builds and runs a Zoho CRM COQL query using the get_module_data_coql_builder endpoint. Instead of typing the full COQL statement, provide the module, selected fields, filter condition, and optional grouping or sorting. The connector generates the final COQL query and sends it to Zoho's /coql API.

This example queries the Leads module, groups records by Country and Lead_Status, and returns a COUNT(id) value for each group.

Use API v7 or v8 for standard modules when you want larger pages, up to 2000 rows per API call. API v2 is limited to 200 rows per COQL call. Zoho COQL offset pagination has a documented window limit: v7/v8 allow up to 100,000 rows for the same query criteria, while v6 documents a 10,000-row offset window. For larger pulls, split the query using a stable condition such as id > last_record_id and repeat the request.

Aggregate functions such as COUNT, SUM, MIN, MAX, and AVG must be uppercase. When mixing aggregate functions with regular fields, every regular field in Select must also be listed in GroupBy.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

SELECT *
	FROM get_module_data_coql_builder
	WITH(
		  Module = 'Leads'
		, Version = 'v8'
		, PageSize = '2000'

		-- Select regular fields and aggregate functions.
		-- Aggregate function names must be uppercase: COUNT, SUM, MIN, MAX, AVG.
		, Select = 'Country,Lead_Status,COUNT(id)'

		-- Enter condition only. Do not include the WHERE keyword.
		, Where = 'Lead_Status=''Junk Lead'' and Created_Time>''2015-07-08T03:07:16-04:00'''

		-- Required because Select mixes regular fields with COUNT(id).
		, GroupBy = 'Country,Lead_Status'

		-- Enter sort expression only. Do not include the ORDER BY keyword.
		, OrderBy = 'Country desc'
	)

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_ZOHO_CRM_IN_GATEWAY], 'SELECT *
	FROM get_module_data_coql_builder
	WITH(
		  Module = ''Leads''
		, Version = ''v8''
		, PageSize = ''2000''

		-- Select regular fields and aggregate functions.
		-- Aggregate function names must be uppercase: COUNT, SUM, MIN, MAX, AVG.
		, Select = ''Country,Lead_Status,COUNT(id)''

		-- Enter condition only. Do not include the WHERE keyword.
		, Where = ''Lead_Status=''''Junk Lead'''' and Created_Time>''''2015-07-08T03:07:16-04:00''''''

		-- Required because Select mixes regular fields with COUNT(id).
		, GroupBy = ''Country,Lead_Status''

		-- Enter sort expression only. Do not include the ORDER BY keyword.
		, OrderBy = ''Country desc''
	)')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_ZOHO_CRM_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
	FROM get_module_data_coql_builder
	WITH(
		  Module = ''Leads''
		, Version = ''v8''
		, PageSize = ''2000''

		-- Select regular fields and aggregate functions.
		-- Aggregate function names must be uppercase: COUNT, SUM, MIN, MAX, AVG.
		, Select = ''Country,Lead_Status,COUNT(id)''

		-- Enter condition only. Do not include the WHERE keyword.
		, Where = ''Lead_Status=''''Junk Lead'''' and Created_Time>''''2015-07-08T03:07:16-04:00''''''

		-- Required because Select mixes regular fields with COUNT(id).
		, GroupBy = ''Country,Lead_Status''

		-- Enter sort expression only. Do not include the ORDER BY keyword.
		, OrderBy = ''Country desc''
	)'
EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY]