---
title: "ZCQL V2 Syntax and Exceptions"
url: https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/
description: "ZCQL is Catalyst's own query language modelled after familiar query languages that you can use to query your app's database efficiently."
last_updated: 2026-08-28T06:26:16Z
service: Cloud Scale
type: general
---

# ZCQL V2 Syntax and Exceptions

Catalyst has added several syntax upgrades and enhancements to help you write ZCQL queries more effectively. These new syntax upgrades will provide additional functionalities and enable you to perform data related operations in the Data Store with much ease.

> **Note:**

* To use these feature enhancements, you will need to migrate to the **V2** version. The syntax upgrades will not be applicable if you implement them while executing ZCQL commands in **V1**.

* From **December 01st, 2024**, all your current projects in all your [Orgs](https://docs.catalyst.zoho.com/en/getting-started/catalyst-organizations/) present in the [Development Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/development-environment/) will be automatically mapped to **ZCQL V2 Parser**.

* From **April 01st, 2025**, all the projects present in all *Orgs* that have already been mapped to **ZCQL V2 Parser** in *Development Environment* will be automatically mapped to **ZCQL V2 Parser** in the [Production Environment](https://docs.catalyst.zoho.com/en/deployment-and-billing/environments/production-environment/), if and when production is enabled for the project.

The following exception and syntax nuances need to be kept in mind when you execute a ZCQL query:

1. The [`AVG ()`](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/#avg) ZCQL function can be applied on the following data types:
    - **Date**
    - **DateTime**
    - **Boolean**

2. When you are indicating a value in your ZCQL statement, indicate the value in single quotes.

    For example, if you have to fetch a particular name from an employee database, you will write the command in the following manner:

```sql
SELECT Name FROM Employee_DB WHERE name = 'Amelia'
```

3. If a column is of Boolean data type:
    - No string values are permitted.
    - Boolean data type will only support the following values:
        - FALSE
        - TRUE
        - NULL

4. If you are using an **Encrypted datatype**, then the following ZCQL functions cannot be used on that column:
    - `AVG()`
    - `SUM()`
    - `MAX()`
    - `MIN()`

5. [Appropriate exceptions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/#list-of-zcql-exceptions) have been added when you use comparative operators like `Less Than`, `Greater Than`, `Less Than or Equal`, and `Greater Than or Equal`, on **Encrypted Text**, and **Boolean** data type. These exceptions are not applicable for `Equal` and `Not Equal` operators.

6. Subqueries are now supported in `WHERE` clause statements. You can find out more about them from this [help document](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/where/#subqueries-in-where-clause).

7. [`ORDER BY`](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/groupby-orderby/#order-by-statement) clause now supports the use of [ZCQL functions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/).

8. In all columns where you use the `ORDER BY` clause, the `ASC` and `DESC` functionality can now be applied. You can also apply `ASC` and `DESC` for individual columns.

**Example Database**:

Consider the following database - **ZylkerEmployeeCompensation**

<table class="content-table">
	<thead>
		<tr>
			<th class="w50p">Name</th>
			<th class="w25p">Salary</th>
            <th class="w25p">Reimbursement</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>Michelle Mascarenhas</td>
			<td>90000</td>
            <td>10000</td>
		</tr>
        <tr>
			<td>Bruce Kyle</td>
			<td>3000000</td>
            <td>45000</td>
		</tr>
        <tr>
			<td>Clark Kane</td>
			<td>3300</td>
            <td>1500</td>
		</tr>
        <tr>
			<td>Judith Bridges</td>
			<td>4700</td>
            <td>300</td>
		</tr>
        <tr>
			<td>Devon Dreymond</td>
			<td>80000</td>
            <td>2300</td>
		</tr>
        <tr>
			<td>Ashley Kim</td>
			<td>6000</td>
            <td>200</td>
		</tr>
	</tbody>
</table>

Execute the following command to list the table

```sql
SELECT Salary, Reimbursement FROM ZylkerEmployeeCompensation ORDER BY Salary ASC, Reimbursement DESC
```

**Output:** 

<table class="content-table">
	<thead>
		<tr>
			<th class="w50p">ZylkerEmployeeCompensation.Salary</th>
            <th class="w50p">ZylkerEmployeeCompensation.Reimbursement</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>3300</td>
            <td>45000</td>
		</tr>
        <tr>
			<td>4700</td>
            <td>10000</td>
		</tr>
        <tr>
			<td>6000</td>
            <td>2300</td>
		</tr>
        <tr>
			<td>80000</td>
            <td>1500</td>
		</tr>
        <tr>
			<td>90000</td>
            <td>300</td>
		</tr>
        <tr>
			<td>3000000</td>
            <td>200</td>
		</tr>
	</tbody>
</table>

9. [Appropriate exceptions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/syntax-exceptions/#list-of-zcql-exceptions) will be triggered in the following cases:
    - If the permitted max/min value has been exceeded in **Integer** datatype.
    - If the character length is longer than the value set by you in **VarChar** datatype.
    - If the value is beyond the supported range in **BIGINT** datatype.

10. You can write **four** [`JOIN`](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/joins/) clauses in a single ZCQL statement. However, you can only write **one** `JOIN` condition for each join clause.

11. **Table Alias** functionality is now supported in ZCQL. You can now invoke alias functionality in ZCQL using the `AS` ZCQL command. Using this command you can refer to your table with other names.

**Example Database**:

Consider the following databases:

**Table 1: ZylkerEmployeeCompensation**

<table class="content-table">
	<thead>
		<tr>
			<th class="w50p">Name</th>
			<th class="w25p">Salary</th>
            <th class="w25p">Reimbursement</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>Michelle Mascarenhas</td>
			<td>90000</td>
            <td>10000</td>
		</tr>
        <tr>
			<td>Bruce Kyle</td>
			<td>3000000</td>
            <td>45000</td>
		</tr>
        <tr>
			<td>Clark Kane</td>
			<td>3300</td>
            <td>1500</td>
		</tr>
        <tr>
			<td>Judith Bridges</td>
			<td>4700</td>
            <td>300</td>
		</tr>
        <tr>
			<td>Devon Dreymond</td>
			<td>80000</td>
            <td>2300</td>
		</tr>
        <tr>
			<td>Ashley Kim</td>
			<td>6000</td>
            <td>200</td>
		</tr>
	</tbody>
</table>

**Table 2: ZylkerEmployeeRelation**

<table class="content-table">
	<thead>
		<tr>
			<th class="w50p">Manager</th>
			<th class="w50p">Member</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>Michelle Mascarenhas</td>
			<td>Bruce Kyle</td>
		</tr>
        <tr>
			<td>Michelle Mascarenhas</td>
			<td>Clark Kane</td>
		</tr>
        <tr>
			<td>Judith Bridges</td>
			<td>Michelle Mascarenhas</td>
		</tr>
	</tbody>
</table>

Execute the following command to list the table

```sql
SELECT Managers.Name, Members.Name FROM ZylkerEmployeeCompensation AS Managers
INNER JOIN ZylkerEmployeeRelation AS asso ON Managers.ROWID = asso.Manager
INNER JOIN ZylkerEmployeeCompensation AS Members ON Members.ROWID = asso.Member
```

**Output:** 

<table class="content-table">
	<thead>
		<tr>
			<th class="w50p">Manager.Name</th>
			<th class="w50p">Member.Name</th>
		</tr>
	</thead>
	<tbody>
		<tr>
			<td>Michelle Mascarenhas</td>
			<td>Bruce Kyle</td>
		</tr>
        <tr>
			<td>Michelle Mascarenhas</td>
			<td>Clark Kane</td>
		</tr>
        <tr>
			<td>Judith Bridges</td>
			<td>Michelle Mascarenhas</td>
		</tr>
	</tbody>
</table>

> **Note:** The table Alias operation is only supported for `SELECT` statements.

12. You can check if you have a NULL value by using the **IS/IS NOT** operator. The **IS/IS NOT** operator support is only available for NULL values.

13. When writing [`GROUP BY`](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/groupby-orderby/) with [`Binaryof()`](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/groupby-orderby/#binaryof-function) statements, the following ZCQL rules need to be remembered:
    - The Binaryof() functionality can only be used in GROUP BY statements.
    - Binaryof() can only be used on columns with VarChar or Text datatype.
    - When Binaryof() is used in a GROUP BY statement, the returned values are case-sensitive. For example, consider you have the values 'AMELIA', 'amelia', 'Burrows', and 'burrows' in a column called Name, in a table called Zylker_EMP. If you use the following command:

```sql
Select Name from Zylker_EMP group by binaryof(Name)
```

The result could be any of the following:

- AMELIA
- amelia
- burrows
- Burrows

14. Column names can now contain numerical values. You can signify such column names in the query using a backtick  (**`**) punctuation. 

For example, a `SELECT` query on a table called Numbers with a column named 01 can be written in the following manner:

```sql
SELECT `01` FROM Numbers
```

### List of ZCQL Exceptions

The following is a list of common exceptions and example query statements that can trigger these exceptions. Ensure that you go through the list, and follow ZCQL syntax strictly to avoid triggering these exceptions.

<table class="content-table">
<thead>
  <tr>
    <th class="w15p">Datatype</th>
    <th class="w20p">Case</th>
    <th class="w25p">Sample ZCQL Query</th>
    <th class="w40p">Triggered Exception</th>
  </tr>
</thead>
<tbody>
  <tr>
    <td rowspan="5"><strong>Encrypted Text</strong></td>
    <td>Certain operators are not supported</td>
    <td>SELECT * FROM temp WHERE enc > 'temsp'</td>
    <td>`Operator > is not supported for
ENCRYPTED TEXT`

The same exception applies, but the exception message will be altered accordingly if you use these operators **<**, **<=**, **>=** in the ZCQL statement</td>
  </tr>
  <tr>
    <td>`LIKE` is not supported</td>
    <td>SELECT  * FROM temp WHERE enc LIKE '*temsp'</td>
    <td>`Operator LIKE is not supported for
ENCRYPTED TEXT`

The same exception applies, but the exception message will be altered accordingly if you use **NOT LIKE** in the ZCQL statement</td>
  </tr>
  <tr>
    <td>`BETWEEN` is not supported</td>
    <td>SELECT * FROM temp WHERE enc BETWEEN 'one' AND 'ten'</td>
    <td>`Operator BETWEEN is not supported for
ENCRYPTED TEXT`

The same exception applies, but the exception message will be altered accordingly if you use **NOT BETWEEN** in the ZCQL statement</td>
  </tr>
  <tr>
    <td>`IN` is not supported</td>
    <td>SELECT * FROM temp WHERE enc IN ('one','three')</td>
    <td>`Operator IN is not supported for
ENCRYPTED TEXT`
    
The same exception applies if you use **NOT IN** in your query, but the exception message will be altered accordingly</td>
  </tr>
    <tr>
    <td>ZCQL functions is not supported</td>
    <td>SELECT AVG(enc) FROM temp</td>
    <td>`AVG() function is not supported for
ENCRYPTED_TEXT`
    
[ZCQL Functions](https://docs.catalyst.zoho.com/en/cloud-scale/help/zcql/zcql-functions/) are not supported in **Encrypted Text**.</td>
  </tr>
  <tr>
    <td rowspan="4"><strong>Boolean</strong></td>
    <td>Certain operators are not supported</td>
    <td>SELECT * FROM temp WHERE bools > FALSE</td>
    <td>`Operator > is not supported for BOOLEAN`

The same exception applies, but the exception message will be altered accordingly if you use these operators **<**, **<=**, **>=**  in the ZCQL statement</td>
  </tr>
  <tr>
    <td>`LIKE` is not supported</td>
    <td>SELECT * FROM temp WHERE bools LIKE FALSE</td>
    <td>`Operator LIKE is not supported for BOOLEAN`

The same exception applies if you use **NOT LIKE** in your query, but the exception message will be altered accordingly</td>
    </tr>
    <tr>
        <td>`BETWEEN` is not supported</td>
        <td>SELECT * FROM temp WHERE bools BETWEEN TRUE and FALSE</td>
        <td>`Operator BETWEEN is not supported for
BOOLEAN`

The same exception applies if you use **NOT BETWEEN** in your query, but the exception message will be altered accordingly</td>
    </tr>
    <tr>
        <td>`IN` is not supported</td>
        <td>SELECT * FROM temp WHERE bools IN (TRUE, FALSE)</td>
        <td>`Operator IN is not supported for BOOLEAN`

The same exception applies if you use **NOT IN** in your query, but the exception message will be altered accordingly</td>
    </tr>
    <tr>
        <td rowspan="1"><strong>VarChar</strong></td>
        <td>Maximum length exceeded</td>
        <td>INSERT INTO temp (Fishes) value ('salmon')</td>
        <td>`'salmon' data too long for column 'Fishes'`

The column `Fishes` was created as a **VarChar** and the **MaxLength** was set as `5`. In this statement, `'Salmon'` exceeds the **MaxLength**</td>
    </tr>
    <tr>
        <td rowspan="1"><strong>Integer</strong></td>
        <td>Maximum value exceeded</td>
        <td>INSERT INTO temp (numbers) VALUE (546895326908)</td>
        <td>`For column 'ints' INT value should be between
-9999999999 and 9999999999`

The column numbers was created as **INT**. The value of **INT** datatype should be between `-9999999999` and `9999999999`</td>
    </tr>
    <tr>
        <td rowspan="1"><strong>Big Integer</strong></td>
        <td>Maximum value exceeded</td>
        <td>INSERT INTO temp (numbers) VALUE (92343432412351
435123453245)</td>
        <td>`Given numeric value 92343432412351
435123453 is too large`

The column numbers was created as **BIGINT**. The value of **BIGINT** datatype should be between `-9223372036854775808` and `9223372036854775807`</td>
    </tr>
    <tr>
        <td rowspan="1"><strong>Text</strong></td>
        <td>Value not enclosed in single quotes</td>
        <td>SELECT * FROM temp WHERE name = one</td>
        <td>`Unknown Table temp or Unknown Column
one in WHERE`

The value `one` will be considered as column if it is not enclosed in quotes-'`one`'</td>
    </tr>
</tbody>
</table>