Skip to main content

Overview

Cinchy Query Language (CQL) ​

Cinchy Query Language (CQL) is a specialized query language designed for managing and retrieving data and metadata across multiple tables in your network, consolidating it into single, actionable outputs. With CQL, you can:

  • Use the query editor for tasks like data retrieval or modification.
  • Work with table views.
  • Manage table indexes.

For a comprehensive list, see the CQL functions Reference List.​

tip

All CQL queries are secured by default through universal data access controls. This ensures you only access data you're authorized to see.

Basic rules ​

CQL, unique to Cinchy, shares similarities with SQL and PGSQL but has key differences. The list below defines the foundational elements of CQL.

  • Query Builder UI: Cinchy's Query Builder UI pre-fills basic syntax to speed up your query process. You can add terms manually or drag onto the Query Builder. More details can be found on the Saved Queries page.
  • Built-in exclusion of deleted data: Queries generated by the Query Builder automatically exclude deleted data with a "WHERE [Deleted] IS NULL" clause. Remove the clause to include deleted records.

Query essentials​

  • Running any SELECT query where data is returned in the Query Window will run, and return, three times (once for the headers, once for the count, once for the data). INSERT statement will therefore insert 3 times. To mitigate this, access the query via the API or select Single Value Response in th Query Builder.
  • Always use the "[Domain].[Table]" format when querying tables. For instance, querying the Product Content Backlog table would follow the [Product].[Product Content Backlog] syntax.
  • For text or string data, encapsulate the text with single quotes, as in [Domain] = 'Sandbox'.
  • Use != to specify Not equal to. For example, [Domain] != 'Sandbox'.
  • A * after SELECT returns system columns for each entry.
  • Use RESOLVELINK for linked column values. Syntax: ResolveLink('value(s)','column in target table').
INSERT INTO [Human Resources].[Employee RY] ([First Name], [Last Name], [Employee ID], [Manager])
VALUES (@FirstName, @LastName, @EmployeeID, (RESOLVELINK(@Manager, ‘Full Name’))

Linked data​

Syntax for linked columns​

For linked columns, employ the "[Column Name].[Linked Column Name]" format.

  • Example: To extract [Full Name] from the Users table through a linked column Requester, use [Requester].[Full Name].
SELECT [Requester].[Full Name]
FROM [Product].[Product Content Backlog]
WHERE [Deleted] IS NULL

You can access nested linked columns using extended syntax, like [Column Name].[Linked Column Name].[Linked Column Name]. For example, if you wanted to see the Manager ID of a specific employee, use [Employee].[Reports To].[Employee ID] to find the employee, who they report to, and their ID number.

SELECT [Employee].[Reports To].[Employee ID]
FROM [Employee Success].[Employees]
WHERE [Deleted] IS NULL

When doing a DELETE or UPDATE on linked data (data where there is a JOIN), you must add an ALIAS to the query. This is to ensure that the query builder knows which tables to use in the function.

Failure to do so may result in the below error:
Ambiguous column name 'XXX'.

Here is an example of an improperly formed DELETE:

DELETE FROM [HR].[People]
WHERE [Deleted] IS NULL
AND [Employee Name] = 'Keith'

Here is an example of the correct syntax, using 'name' as the alias

DELETE name FROM [HR].[People] name
WHERE name.[Deleted] IS NULL
AND name.[Employee Name] = 'Keith'

The general syntax is below:

DELETE alias FROM [Domain].[Table] alias
WHERE alias.[Deleted] IS NULL
AND alias.[Linked Column] = 'string'

Function specific rules​

  • For INSERT INTO, ensure column and value order matches.

Draft and version handling​

  • Query draft rows using Draft([Column Name]). Also include [Column Name] for non-draft rows.
  • Default sorting is ascending unless specified.
  • In Boolean queries, 1 means true, 0 means false.
  • In Cinchy, version history labeling diverges from standard SQL systems. While typical systems use a single label like "version 1.2.4," Cinchy breaks this into two components: Version and Draft Data. These serve as ORDER BY options. For example, if Version is 2 and Draft Data is 5, the complete version is denoted as 2.5.

Query return results ​

You can specify what your results return as in the Query Builder

Query result options​

The table below lists what your results can return:

Query return resultDescription
Query Result (Approved Data Only)Default return type. Returns table data that's approved. Ideal for use with external APIs.
Query Result (Including Draft Data)Displays records, including those that are pending approval.
Query Result (Including Version History)Returns a table from a SELECT query (including draft data) with historical data for all tables, as seen in the Collaboration Log, including all changes within the scope of the query.
Number of Rows AffectedReturns a single string response of the number of rows affected if the last statement is an INSERT, UPDATE, or DELETE.
Execute DDL ScriptFor queries with DDL (Data Definition Language) commands like CREATE, ALTER, DROP.
Single ValueReturns a result of 1 row x 1 column, irrespective of the underlying result set.