CQL Examples

This section breaks down some example queries for you to get a feel for the power of Cinchy Query Language (CQL) and how to use it. These simple examples provide a point of reference for you to create more complex queries.

For more information on saved queries, see our Builder Guide.

Querying a Query

You can query a saved query in two ways.

The exec function will execute your query as is.

Syntax

exec[OtherQueryDomain].[OtherQueryName]

Example

exec[Product].[Get Ticket Priority]

You can also query it in the context of a SELECT statement, which allows you to directly reference the saved query results within the context of the new query. This is useful for cases when you want to further manipulate the data, such as through a JOIN.

Syntax

SELECT a.*
FROM [OtherQueryDomain].[OtherQueryName] a

Considerations

• a is used as an alias. Ensure that whatever alias you choose to use must not be used in the query that is being called.

Example

SELECT a.*
FROM [Product].[Get Ticket Priority] a

Create a list of specific users

This query is useful for pulling a list of specific user information from a table. This query will return a list with Names (which will be returned under the 'Full Name' label), Emails, and Companies of the users listed in the [Contacts].[People] table. This example only includes entries where the [Name] isn't null, and where the [Tags].[Name] column is set to 'Analyst'

Syntax

SELECT [Column1] as 'Alias',[Column2], [Column3]
FROM [Domain].[Table]
WHERE [Deleted] IS NULL
AND [Column1] IS NOT NULL
AND [Column4].[Value1]= 'Value2'
ORDER BY [Column1]

Example

SELECT [Name] as 'Full Name',[Email], [Company]
FROM [Contacts].[People]
WHERE [Deleted] IS NULL
AND [Name] IS NOT NULL
AND [Tags].[Name]= 'Analyst'
ORDER BY [Name]

Find all tables in a specific domain

In this example we want to find all System Tables in our instance. System Tables come pre-packaged with Cinchy and live under the 'Cinchy' domain. You can query for a list of them using the below CQL, where the query pulls the 'Name' column from the [Tables] table, in the [Cinchy] Domain. There's also a filter to only return results where the table [Domain] is tagged as 'Cinchy**'**.

Syntax

SELECT [Column1]
FROM [Domain].[Table]
WHERE [Deleted] IS NULL
AND [Domain] = 'Value1'

Example

SELECT [Name]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NULL
AND [Domain] = 'Cinchy'

Find system columns attached to a table

In this example, we want to generate a list of the system columns attached to the [Policies] table in the [Compliance] domain. We do so by putting the * symbol in the SELECT statement. There's also an optional ORDER BY clause, to order results based on the input values.

Syntax

SELECT *
FROM [Domain].[Table]
WHERE [Deleted] IS NULL
ORDER BY [Column1], [Column2], [Column3], [Column4]

Example

SELECT *
FROM [Compliance].[Policies]
WHERE [Deleted] IS NULL
ORDER BY [Cinchy Id], [Title], [Version], [Draft Version]

Create a custom View for a logged-in user

This example has a table with a view for My Open Tasks. This view uses currentuserID=(), which only shows the tasks assigned to the user currently logged in.

Example

[Assignee].[Person].[Cinchy User Account].[Cinchy Id] = currentuserID()

Finding the owner of a table

The below query finds the creator of any specific table, based on its tablecinchyid. The query pulls the Table Name and the Created By user information from the [Cinchy].[Tables] table. By using [Cinchy Id] = @tablecinchyid, a search box will appear when you run this query, where you can insert the Table ID number of the table you are curious about.

Example

SELECT [Name], [Created By]
FROM [Cinchy].[Tables]
WHERE [Deleted] is null and [Cinchy Id] = @tablecinchyid

Find all tables created by a specific user

The example below uses a query to return a list of all tables created by a specific user. It pulls the Full Name of the table and the name of the creator from the [Cinchy].[Tables] table. By using [Created By].[Name] = @author, running the query will generate a search box where you can write in the full name of the user you are searching for information on.

Example

SELECT [Full Name], [Created By].[Name]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NULL AND [Created By].[Name] = @author

If/Else statements

You can use If/Else statements in CQL to specify conditions. The example below uses a query to vote for user awards, and uses IF statements to look for the currentuserid() to determine the results of the query.

Example

IF( @NOMINEE_USER  = currentuserid())
Begin
SELECT 'Hey! No voting for yourself!'
 End
ELSE
IF( @CREATEDBY_USER = currentuserid())
Begin
SELECT 'Hey! No voting for your own nomination!'
 End
 ELSE
Begin
   		INSERT INTO [Employee Success].[BASICs In Action Votes] ([Nomination], [Vote]) VALUES (ResolveLink(@NOMINATION_ID, 'Cinchy Id'), @VOTE)
		SELECT 'Your vote has been recorded. Great work!'
End

If the current user is the same as the nominated user, the query will return a message stating "Hey! No voting for yourself!"

IF( @NOMINEE_USER  = currentuserid())
Begin
SELECT 'Hey! No voting for yourself!'
 End

The second condition checks whether the current user is the same as the user who created the nomination. In this case, the query will return a message stating "Hey! No voting for your own nomination!"

ELSE
IF( @CREATEDBY_USER = currentuserid())
Begin
SELECT 'Hey! No voting for your own nomination!'
 End

If neither of the two IF statements apply, the final ELSE triggers to capture the vote (using the INSERT INTO statement), and a message is returned stating "Your vote has been recorded. Great work!"

 ELSE
Begin
   		INSERT INTO [Employee Success].[BASICs In Action Votes] ([Nomination], [Vote]) VALUES (ResolveLink(@NOMINATION_ID, 'Cinchy Id'), @VOTE)
		SELECT 'Your vote has been recorded. Great work!'
End

Find all Deleted Tables

This query returns the Name, Domain, and timestamp of deletion for all tables within [Cinchy].[Tables] where [Deleted] IS NOT NULL. We've added an option WHERE clause to ignore any data from the 'Sandbox' Domain.

Example

SELECT [Name], [Domain], [Deleted]
FROM [Cinchy].[Tables]
WHERE [Deleted] IS NOT NULL
	AND [Domain] != 'Sandbox'

Returning the Change History

Every update to a record creates a new version, which can be viewed via the [Version] system column. This query will return each version of the named [Ticket] from the [Product].[Release Notes] table, as well as the time that version was created.

Ensure that the query return type is set as "Query Results (Including Version History )".

SELECT [Version], [Created], [Ticket]
FROM [Product].[Release Notes]
WHERE [Deleted] IS NULL AND [Ticket] = 'CIN-08103'

Example Results