Overview

This page will help you to understand the fundamentals of getting started with Cinchy Query Language.

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 master list.​ for details.

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.

Syntax for table queries

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.

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

Multi-level links

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

Query essentials

• 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'.

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.

Error and syntax guidance

• Errors will show in the Query Builder’s results pane.

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

System and link-related queries

• 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’))

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 result	Description
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 Affected	Returns a single string response of the number of rows affected if the last statement is an INSERT, UPDATE, or DELETE.
Execute DDL Script	For queries with DDL (Data Definition Language) commands like CREATE, ALTER, DROP.
Single Value	Returns a result of 1 row x 1 column, irrespective of the underlying result set.
### Data return types

The following tables show the data types that a Cinchy Data Type translates to in the database:

Number	Text	Date
Int	NVarChar	DateTime
BigInt	VarChar	Date
Decimal	Char	​
Float	NChar	​
Money	NText	​
Numeric	Text	​
Real	​	​
SmallInt	​	​
SmallMoney	​	​
TinyInt	​	​

Binary	Text
VarBinary	Bit