The Basics of CQL
This page will help you to understand the fundamentals of getting started with Cinchy Query Language.
1. Introduction
Cinchy Query Language (CQL) is a query language unique to dataware that is used to retrieve and manage/modify data and metadata from tables in your network. While data can reside across many tables, a query can isolate it to a single output, making the possibilities of CQL endlessly powerful.
Cinchy Query Language can be used in many ways, including but not limited to:
Building queries through the query editor that can return, insert, delete, and otherwise manage your data.
Creating, altering or dropping views for tables
Creating, altering or dropping indexes
For an overview of the supported functions of CQL, see here.
All queries on Cinchy are automatically protected by universal data access controls. This means that if you run a CQL query, you will only see the data that you have been given access to.
2. Basic Rules of CQL
The following is a non-exhaustive list of some of the common things to keep in mind while using CQL. While CQL pulls many similarities to other queries languages such as SQL or PGSQL that you may already be familiar with, there are still differences to make note of.
In all queries built using the Cinchy Query Builder, you will note that it includes a "WHERE [Deleted] IS NULL" clause. This will prevent any data that has been deleted from a table from ending up in your query. If you want to include deleted data, you must delete this clause.
When querying a table, you must use the [Domain].[Table] syntax. For example, to query the Product Content Backlog table, you would use [Product].[Product Content Backlog].
When querying a linked column, you must similarly use the [Column Name].[Linked Column Name] syntax.
For example, in the below query we are pulling from the [Product].[Product Content Backlog] table, and are looking for data from a linked column called "Requester", which points to the Users table. In order to return the [Full Name] column from the linked Users table, we must use the [Requester].[Full Name] syntax.
Multi-level links are also possible using a similar syntax of [Column Name].[Linked Column Name].[Linked Column Name]. For example, if you wanted to see the Manager ID of a specific employee, you could use [Employee].[Reports To].[Employee ID], which will find the Employee in question, then who they report to, then the ID number of that person.
When writing queries, single notation marks are used to denote string/text data. For example, if you wanted to specify the Sandbox domain, you would use [Domain] = 'Sandbox'
When trying to query using a "does not equal" syntax, you would use !=. For example, the following denotes to only return results where the [Domain] does not equal 'Sandbox'
If you want to query data from a table where certain rows are still in draft/Create Request format, you would use the syntax of Draft([Column Name]) to see the draft changes. Make sure to also include [Column Name] as well to include non-draft rows.
The default ORDER BY function will always set ascending unless specified.
When using a boolean query, 1 = true, and 0 = false.
Version history in Cinchy is labeled differently in other SQL systems. Normally a version history is shown through, for example, "version 1.2.4". Cinchy follows these conventions but splits them up. The two ORDER BY options, Version and Draft Data. are the version numbers. Version is the first number, and Draft Data is the second number in the sequence Example: [Version]: 2 and [Draft Data]: 5 means the overall version of the policy is 2.5
If there is an error in your CQL code an error message will appear in your Query Results in the query builder indicating what column and row your error(s) reside in.
When using an INSERT INTO clause, the order of the columns input must match the same order as in the VALUES section.
Putting a * symbol after a SELECT statement in the Query Builder will return a series of system columns attached to each table entry.
Use RESOLVELINK when inserting or updating values for link columns. In the below example, we use (RESOLVELINK(@Manager,'Full Name')) because the Manager column is a linked column pulling from the Employee table. The syntax for using Resolve Link is: ResolveLink('value(s)','column in target table')
3. Query Return Results
You can specify what your results return as in the Query Builder
3. Data Return Types
The following tables provide the data type(s) that a Cinchy Data Type translates to in the database:
Last updated