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.

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

1. The 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:

    
   
        
   copy
        
   
            
   
        
    
    
   
         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 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.

7. ORDER BY clause now supports the use of 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

Execute the following command to list the table

    

        
copy
        

            

        
    
    

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

Output:

9. Appropriate 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 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 2: ZylkerEmployeeRelation

Execute the following command to list the table

    

        
copy
        

            

        
    
    

         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:

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 with Binaryof() 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:
      

    
        
    copy
        
    
            
    
        
    
    
         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:

    

        
copy
        

            

        
    
    

         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.