1. Overview

The Cinchy Query Language (CQL) statements are grouped into two (2) categories:

• Data Manipulation Language (DML) - DML is used to add, retrieve, update and manipulate data. For example, insert, update, delete.

• Data Definition Language (DDL) - DDL is used to create a database schema and define constraints. For example, create a table and alter a table.

2. Data Manipulation Language (DML)

List of available DML statements:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• ​Declare Variables​

• ​Set Variables​

3. Data Definition Languages (DDL)

List of available DDL statements:

• ​Create Table​

• ​Alter Table​

• ​Drop Table​

• ​Create View​

• ​Alter View​

• ​Drop View​

• ​Create Index​

• ​Drop Index​

1. Overview

DDL is used to create a database schema and define constraints.The Cinchy DDL functions covered on this page are:

• ​Create Table​

• ​Alter Table​

• ​Drop Table​

• ​Create View​

• ​Alter View​

• ​Drop View​

• ​Create Index​

• ​Drop Index​

Data Type Translations

The following tables provide the data type(s) that a Cinchy Data Type translates to in the database:

Create Table

The CREATE TABLE statement is used to create a new table in a database.

The column parameters specify the names of the columns of the table.The datatype parameter specifies the type of data the column can hold (e.g. varchar, char, int, date, etc.).

Syntax

CREATE TABLE [Domain].[Table_Name] (
 [Column1] [datatype],
 [Column2] [datatype],
 [Column3] [datatype]
)

Example

CREATE TABLE [HR].[Employees] (
 [Employee_ID] INT,
 [LastName] VARCHAR(255),
 [FirstName] VARCHAR(255),
 [Start Date] SMALLDATETIME
)

Alter Table

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax

ALTER TABLE [Domain].[Table_Name]
ADD [column_name] datatype

Example

ALTER TABLE [HR].[Employees]
ADD [Email] VARCHAR(255)

Drop Table

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE [Domain].[Table_Name]

Example

DROP TABLE [HR].[Employees]

Create View

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, taken from fields of an original table and presents them with a different structure and organization. For example, this is useful when only some columns of a table may be relevant to a user, so they don't have to see information from other columns that may be irrelevant to them.

SQL functions and the WHERE statement can be added to a view to present the data as if the data was coming from one single table.

Syntax

CREATE VIEW [Domain].[View_Name] AS
SELECT [Column1], [Column2], ...
FROM [Domain].[Table_Name]
WHERE [condition]

Example

CREATE VIEW [Detractors] AS
SELECT [Client Name].[Full Name], [Client Name].[Eamil Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Wealth].[Net Promoter Score]
WHERE [Q1] BETWEEN 0 AND 6

Alter View

Modifies a previously created view. This includes an indexed view. ALTER VIEW does not affect dependent stored procedures or triggers and does not change permissions.

Syntax

ALTER VIEW [Domain].[View_Name]   
AS <select_statement>

Example

ALTER VIEW [Wealth].[Detractors]
AS
SELECT [Client Name].[Full Name], [Client Name].[Eamil Address], [Advisor].[Full Name], [Q1], [Q2]
FROM [Wealth].[Net Promoter Score]
WHERE [Q1] BETWEEN 0 AND 6

Drop View

A view is deleted with the DROP VIEW command.

Syntax

DROP VIEW [Domain].[View_Name]

Example

DROP VIEW [Wealth].[Detractors]

Create Index

The CREATE INDEX statement is used to create indexes in tables.

Syntax

CREATE INDEX [index_name]
ON [Domain].[Table_Name] ([Column1],[Column2], ...)

Example

CREATE INDEX [idx_lastname]
ON [HR].[Employees] ([LastName])

Drop Index

The DROP INDEX statement is used to delete an index on a table.

Syntax

DROP INDEX [index_name] ON [Domain].[Table_Name]

Example

DROP INDEX [idx_lastname] ON [HR].[Employees]

1. CQL Examples

The following section breaks down some example queries for you to get a feel for the power of CQL and how to use it. These are just simple examples, but will provide you with a jumping off point for creating your own, more complex queries.

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

1.1 Creating 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. We have stipulated to only include entries where the [Name] is not 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]

1.2 Finding 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 we are pulling the 'Name' column from the [Tables] table, in the [Cinchy] Domain. We have added 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'

1.3 Finding 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. We have also added an option ORDER BY clause, to order our 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] 

1.4 Creating a View that is tailored to the user who is logged in

In this example, we have a table with a view for "My Open Tasks". We want this view to only show tasks that are assigned to the currently logged in user, so we would use currentuserID=().

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

1.5 Finding the owner of a table

The below query is used to find the creator of any specific table, based on its tablecinchyid. We are pulling 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 we run this query, where you can insert the Table ID number of the table you are curious about.

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

1.6 Finding all tables created by a specific user

In the below example, we are using a query to return a list of all tables created by a specific user. We are pulling 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.

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

1.7 Using If/Else statements

If/Else statements can be used in CQL to specify conditions. In the below example, we are using a query to vote for user awards, and our IF statements are looking for the currentuserid() to determine the results of the query.

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

Our 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 is triggered 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

1.8 Finding 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 have added an option WHERE clause to ignore any data from the 'Sandbox' Domain.

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

1. Overview

DML is used to add, retrieve, update and manipulate data.The Cinchy DML statements covered on this page are:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• ​Declare Variables​

• ​Set Variables​

SELECT

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

Note: Table name requires domain prefix.

Syntax

SELECT [Column1],[Column2], ...
FROM [Domain].[Table_Name]

Example

SELECT [Full Name], [Email Address], [Start Date]
FROM [HR].[Employee]

Nested JSONs

You can create a query that will produce a nested JSON by wrapping it in an outer SELECT statement, such as in the example below.

Example Statement

SELECT (
SELECT 
(SELECT p1.[Parent A],p1.[Parent B] 
FROM [QA].[Parents 5080] p1 WHERE p1.[Parents]=p.[Parents]
FOR JSON PATH) As Parents,
(SELECT c.[Name] AS [Child Name]
FROM [QA].[Children 5080] c
WHERE c.[Deleted] IS NULL AND c.[Parents]= p.[Parents]
FOR JSON PATH) AS Children
FROM [QA].[Parents 5080] p
WHERE p.[Deleted] IS NULL
FOR JSON PATH, INCLUDE_NULL_VALUES)

Example Output

[
  {
    "Parents": [{ "Parent A": "Tom", "Parent B": "Maria" }],
    "Children": [
      { "Child Name": "John" },
      { "Child Name": "Theodor" },
      { "Child Name": "Lynette" }
    ]
  },
  {
    "Parents": [{ "Parent A": "Ray", "Parent B": "Sofia" }],
    "Children": [{ "Child Name": "Stephen" }]
  },
  {
    "Parents": [{ "Parent A": "Greg", "Parent B": "Mona" }],
    "Children": [{ "Child Name": "Elizabeth" }, { "Child Name": "Martin" }]
  },
  {
    "Parents": [{ "Parent A": "Henry", "Parent B": "Susanne" }],
    "Children": null
  }
]

INSERT

• An INSERT statement can be used to add new rows to a table or view

• You can also include a SELECT statement to identify that another table or view contains the data for the new row or rows.

Note: The table name requires the domain prefix.

Syntax

INSERT INTO [Domain].[Table_Name] ([Column1],[Column2],[Column3], ...)
VALUES ([Value1],[Value2],[Value3], ...)

Example

INSERT INTO [Contacts].[People] ([First Name],[Last Name],[Address],[Job Title])
VALUES (@firstname, @lastname, @address, @jobtitle)

UPDATE

The data in a table can be changed by using the UPDATE statement.

The UPDATE statement modifies zero or more rows of a table, depending on how many rows satisfy the search condition that was specified in the WHERE clause.

An UPDATE statement can also be used to specify the values that are to be updated in a single row.

• This is done by specifying constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

Note: Table name requires domain prefix.

Syntax

UPDATE [Domain].[Table_Name]
SET [Column1] = [Value1], [Column2] = [value2], ...
WHERE [condition]

Example

UPDATE [Revenue].[Customers]
SET [ContactName] = 'Alfred Schmidt', [City]= 'Frankfurt'
WHERE [Customer_ID] = 1;

DELETE

A DELETE statement can be used to remove entire rows from a table. The number of rows deleted depends on how many rows satisfy the search condition specified in the WHERE statement.

Note: Table name requires domain prefix.

Syntax

DELETE FROM [Domain].[Table_Name] WHERE [condition]

Example

DELETE FROM [Revenue].[Customers] WHERE [CustomerName] = 'Alfreds Futterkiste';

IF

The IF statement is used to execute a condition. If the condition is satisfied then the boolean expressions returns TRUE value. The optional ELSE keyword introduces another statement that is executed when the IF condition is not satisfied and returns FALSE value.

Syntax

IF [condition] THEN [value_if_true] ELSE [value_if_false]

Example

IF 500<1000 THEN 'YES' ELSE 'NO'

Declare Variable

The DECLARE statement is used to declare a variable.

Syntax

DECLARE @variable_name variable_type;

Example

DECLARE @var varchar(50);

Set Variable

The SET variable is used to set a value to a variable.

Syntax

SET @variable_name = variable_value

Example

SET @var = 'Alfreds Futterkiste'

1. Overview

The Cinchy system values covered in this section:

@cinchy_row_id

The @cinchy_row_id function returns the cinchy ID of the last-inserted row.

Syntax

Examples

1. Overview

The following living list documents Cinchy Query Language functions. Click on the function name to be taken to its individual documentation page.Please note that some CQL functions are currently "in progress" for support on PGSQL deployments. Please check back regularly for the updated list.

2. CQL Functions

1. Overview

Cinchy Supported Functions covers the following topics:

• ​

• ​

• ​

• Scalar Functions

Cinchy Functions and Cinchy User Defined Functions (UDFs) are used in conjunction with Cinchy and statements.

Cinchy System Values are specific to the Cinchy platform, similar to Cinchy Functions.

Cinchy User Defined Functions can be used for more customizable logic to accommodate your business requirements.

Scalar functions operate on a single value and return a single value.

​

1. Overview

Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from -- as if it is a table.

2. Using a Table Valued UDF in CQL

The SELECT and FROM clause work the same for a table-valued UDF as they would for a regular Cinchy table.

SELECT u.*
FROM tableUDF() u

3. Creating a Table in a UDF

To generate a table within a UDF for use in CQL, a dataTable will need to be created in the same format as the default Cinchy JSON Saved Query response (Image 1).

function tableUDF()
{
  var result = {};
  result['schema'] = [
    {
      "columnName": "Text Column",
      "type": "String"
    },
    {
      "columnName": "Number Column",
      "type": "Double"
    },
    {
      "columnName": "Date Column",
      "type": "DateTime"
    },
    {
      "columnName": "Yes/No Column",
      "type": "Boolean"
    }
  ];
  result['data'] = [];
  result['data'].push(['Record 1',1,'01/01/2020',false]);
  result['data'].push(['Record 2',2.0,'February 2, 2020',true]);
  result['data'].push(['Record 3',-3,'Mar 03, 2020 12:00:00 AM',false]);
  return JSON.stringify(result);
}

1. Overview

The return date and time part functions covered in this section are:

• DATENAME

• DATEPART

• DAY

• MONTH

• YEAR

DATENAME

The DATENAME function returns a character string representing the specified datepart of the specified date.

Syntax

DATENAME ( datepart , date )

Arguments

datepart The specific part of the date argument that DATENAME will return. This table lists all valid datepart arguments.

date

An expression that can resolve to one of the following data types:

• date

• datetime

• datetimeoffset

• datetime2

• smalldatetime

• time

For date, DATENAME will accept a column expression, expression, string literal, or user-defined variable. Use four-digit years to avoid ambiguity issues.

Return Types

nvarchar

Remarks

Use DATENAME in the following clauses:

• GROUP BY

• HAVING

• ORDER BY

• SELECT <list>

• WHERE

Examples

SELECT DATENAME(datepart,'2007-10-30 12:15:32.1234567 +05:10');

Result Set

DATEPART

DATEPART function returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Arguments

datepart The specific part of the date argument for which DATEPART will return an integer. This table lists all valid datepart arguments.

date An expression that resolves to one of the following data types:

• date

• datetime

• datetimeoffset

• datetime2

• smalldatetime

• time

For date, DATEPART will accept a column expression, expression, string literal, or user-defined variable. Use four-digit years to avoid ambiguity issues.

Return Type

int

Remarks

DATEPART can be used in the select list, WHERE, HAVING, GROUP BY, and ORDER BY clauses.

DATEPART implicitly casts string literals as a datetime2 type in SQL Server 2019 (15.x). This means that DATENAME does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

Example 1: Returns Base Year

SELECT DATEPART(year, 0), DATEPART(month, 0), DATEPART(day, 0)

-- Returns: 1900 1 1

Example 2: Returns Day the Day Part of the Date

SELECT TOP(1) DATEPART(day,[Modified])
FROM [Cinchy].[Domains]
WHERE [Deleted] IS NULL

-- Returns: 20

Example 3: Returns the year Part of the Date

SELECT TOP(1) DATEPART(year,[Modified])
FROM [Cinchy].[Domains]
WHERE [Deleted] IS NULL

-- Returns: 2020

DAY

DAY function returns an integer that represents the day (day of the month) of the specified date.

Syntax

DAY ( date )

Arguments

date An expression that resolves to one of the following data types:

• date

• datetime

• datetimeoffset

• datetime2

• smalldatetime

• time

For date, DAY will accept a column expression, expression, string literal, or user-defined variable.

Return Types

int

Example 1:

This returns 30 - the number of the day itself

SELECT DAY('2015-04-30 01:01:01.1234567')

Example 2:

This statement returns 1900, 1, 1. The date argument has a number value of 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0)

MONTH

MONTH returns an integer that represents the month of the specified date.

Syntax

MONTH ( date )

Arguments

date Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable, or string literal.

Return Types

int

Example 1:

The following statement returns 4. This is the number of the month.

SELECT MONTH('2007-04-30T01:01:01.1234567 -07:00')

Example 2:

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0)

YEAR

YEAR function returns an integer that represents the year of the specified date.

Syntax

YEAR ( date )

Arguments

date Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.

Return Types

int

Example 1:

The following statement returns 2020. This is the number of the year.

SELECT YEAR('2020-04-30T01:01:01.1234567-07:00')

Example 2:

The following statement returns 1900, 1, 1. The argument for date is the number 0. SQL Server interprets 0 as January 1, 1900.

SELECT YEAR(0), MONTH(0), DAY(0)

1. Overview

The Cinchy functions covered in this section are:

• ​resolveLink()​

• ​currentUserID()​

• ​currentUsersGroups()​

• ​executeSavedQuery()​

• ​GetLastModifiedBy()​

• ​draft([Column Name])​

resolveLink()

The resolveLink function is used to insert or update values for link columns. It can be used either with values in the target table or by using the Cinchy Id.

Syntax

ResolveLink('value(s)','column in target table')

'CinchyId,0,CinchyId2,0'

Example

INSERT INTO [Cinchy].[Domain] (Name, Colour)
VALUES ('Test', ResolveLink('Purple','Name'))

INSERT INTO [Corporate].[Projects] (Name, Teams)
VALUES ('CQL Documentation', ResolveLink('Customer Success, Product, Development','Name'))


UPDATE [Product].[Backlog]
SET [Watchers] = '152,0,187,0,347,0'
WHERE [Summary] = 'You can use resolveLink to update link values in DML.'

currentUserID()

The currentUserID() function returns the currently logged in user's Cinchy Id. This is useful for setting up views as well as permissions.

Syntax

[Linked_Column].[Cinchy Id] = currentUserID()

Example

You can use the below example in a view filter so that only the currently logged in user's tasks will appear.

[Assignee].[Cinchy Id] = currentUserID()

currentUsersGroups()

The currentUsersGroups() function returns a list of the Cinchy Ids of groups that the current user belongs to, including any parent groups. For example, if a user is in the Cinchy Product group and the Cinchy Product group is under Cinchy Employees, then both will be returned.

Syntax

[Cinchy Id] IN (currentUsersGroups())

Example

SELECT [Name] FROM [Cinchy].[Groups] 
WHERE [Cinchy Id] IN (currentUsersGroups())

executeSavedQuery()

The executeSavedQuery function returns a scalar or list of scalar values from the saved query specified as the parameters of the function. There are two optional parameters for this function: CacheTimeout and RecordLimitForReturn.

Syntax

[Column] = executeSavedQuery('Domain','Saved Query Name')

Example (Single Value)

[Department] = executeSavedQuery('HR','Get Department')

-- Equivalent to below if you create a Saved Query in the HR domain called
-- Get Department with the following subquery.

[Department] = (SELECT [Team]
                FROM [HR].[Employees] 
                WHERE [Cinchy User Account].[Cinchy Id] = currentUserID())

Example (Multi Value)

[Assignee] IN (executeSavedQuery('HR','Get My Direct Reports')

Optional Parameter: Timeout

A cache expiry timeout for executeSavedQuery is an additional option that can be added. Simply add the number of seconds as a 3rd parameter to the function.

[Department] = executeSavedQuery('HR','Get Department',30)

Optional Parameter: Record Limit

The RecordLimitForReturn parameter limits the amount of records returned. For example: If the query returns 10 records, but you set the parameter to 5, then you will get the first five records back.

[Department] = executeSavedQuery('HR','Get Department',30, 5)

GetLastModifiedBy()

The GetLastModifiedBy([Column]) function will return the CinchyID of the user who last modified the specified column. It is currently only supported in SELECT statements.​

Syntax

GetLastModifiedBy([Column])

Example

This example will return the CinchyID of the user who last modified the Name column in the Employees table.

SELECT getLastModifiedBy([Name])
FROM [HR].[Employees]

draft([Column Name])

This function allows you to query for draft values on tables where Change Approval is enabled.

Syntax

SELECT draft([Column])
FROM [domain].[table]

Example

In this example, we want to query all data in the Employees table, including the data that is pending a change request (Image 1).

To return results that include the draft changes in the First Name column, we would set our query results to Include Draft Data, and use the following syntax (Image 2):

SELECT [Employee ID], draft([First Name]), [Full Name], [Salary], [Date Hired]
FROM [HR].[Employees]

1. Overview

User Defined Functions provide customers with a way to specify and use more particular logic in your solutions than plain CQL may allow. It can be used to simplify calculations and orchestrate automations to accommodate your business requirements.

UDFs are written in Javascript.

There are two (2) groups of Cinchy User Defined Functions (UDF's):

• Table-Valued Functions - Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from as if it is a table.

• Scalar-Valued Functions - Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.

Cinchy UDFs run https://github.com/sebastienros/jint, which uses ECMAScript 5.1.

User Defined Functions (UDFs) are registered in the Cinchy User Defined Functions table (Image 1).

2. Structure

A user defined function in Cinchy is written in Javascript, and comes in the form of:

function name(parameter1, parameter2, parameter3,...) {
  // code to be executed
  return something;
}

It can perform external API calls and execute Cinchy Queries. Generally, at least something should be returned as an indication of success or failure even if you do not want to return any values.

Helper functions can be created within a UDF, however you cannot reference other UDFs in your UDF.

3. Imports

To use advanced functions in UDFs, import the following.

var helpers = importNamespace('Cinchy.UDFExtensions');
var adoNet = importNamespace('Cinchy.AdoNet');
var sysData = importNamespace('System.Data');

4. Cinchy Functions in UDFs

The following functions can be used in a Cinchy User Defined Function (but not in CQL directly).

4.1 External API Call

An XMLHttpRequest() helper can be used to help POST or GET data from an external API. Note that Cinchy basicAuthAPIs can also be accessed this way.

Supported Methods

var xmlHttp = new helpers.XMLHttpRequest();
    xmlHttp.open('POST', 'yourURLhere', false);
    xmlHttp.setRequestHeader('Content-Type', 'application/json');
    xmlHttp.setRequestHeader('apikey', 'yourapikeyhere');
    xmlHttp.send(JSON.stringify(payload));
    if (xmlHttp.status === 200) {
        var response = JSON.parse(xmlHttp.responseText);
        return response.result;
    } else {
        return 'Request failed.';
    }

4.2 Cinchy Query Call

A Cinchy query or a non query (not expecting a result back) can be executed in a UDF as well.

Supported Methods

function mainFunction(query,p1,p2) {
    var sysData = importNamespace('System.Data');
    var param = [];
    param.push(generateCinchyParam('parameterName1', sysData.DbType.String, p1.toString()));
    param.push(generateCinchyParam('parameterName2', sysData.DbType.Double, p2));

    var dataTable = [];
    dataTable = Query.executeQuery(query, param, null, null);
    
    return getSingleValue(dataTable,'colName');
}

function generateCinchyParam(name, type, value){
    var adoNet = importNamespace('Cinchy.AdoNet');
    cinchyParam = new adoNet.CinchyParameter('@' + name, type);
    cinchyParam.value = value;
    return cinchyParam;
}

function getSingleValue(dataTable,colName) {
    var sysData = importNamespace('System.Data');
    var result = '';
    var enumerator = dataTable.Rows.GetEnumerator();
    while (enumerator.MoveNext()) {
        var record = enumerator.Current;
        result = record[colName];
    }
    return result;
}

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

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.

• Cinchy comes with an intuitive Query Builder UI. When using the builder, the below basic syntax is pre-written for you. You can then use the drag and drop interface or type in your own search terms to build your query.​​

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

SELECT [Requester].[Full Name]
FROM [Product].[Product Content Backlog]
WHERE [Deleted] IS NULL

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

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

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

[Domain] != '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.

SELECT *

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

INSERT INTO [Human Resources].[Employee RY] ([First Name], [Last Name], [Employee ID], [Manager])
VALUES (@FirstName, @LastName, @EmployeeID, (RESOLVELINK(@Manager, ‘Full Name’)) 

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:

1. Overview

Perform operations on a date and time input values and return string, numeric, or date and time values.

The date and time date type and functions covered in this section are:

• ​Date and Time Data Types​

• Date and Time Function Categories

  • ​Return System Date and Time Values ​

  • ​Return Date and Time Parts​

  • ​Return Date and Time Values from their Parts​

  • ​Return Date and Time Difference Values​

  • ​Modify Date and Time Values ​

  • ​Validate Date and Time Values​

Date and Time Data Types

​

1. Overview

Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.

2. Using a Scalar-Valued UDF in CQL

Select a scalar value UDF as a column (Image 1).

Sample UDF

function demo(param1, param2) { 
    return param1 + ' ' + param2;
}

Sample Query

SELECT [Domain], [Name], demo([Domain],[Name]) as 'Full Name'
FROM [Cinchy].[Tables]
WHERE [Domain] = 'Cinchy'

3. Using a Scalar-Valued UDF in a Calculated Column

Once it is confirmed that the UDF returns the expected result though a query, a calculated column can be include.

3.1 For Calculations

Depending on how intensive or live you want the calculation to be, choose whether to make it a live or cached calculated column.

Simply add your UDF to the calculated column (Image 2).

3.2 As a Trigger

To use the UDF to trigger an action (ex. create a row in another table), it should be a cached calculated column. Here are a few scenarios to watch out for:

• Do not trigger when you do not have all the necessary fields

• Do not trigger when non-relevant data on the row changes

  • Make sure to appropriately insert and/or update in another table

1. Overview

The modify date and time value functions covered in this section are:

• ​

• ​

• ​

DATEADD

DATEADD function adds a specified number value to a specified datepart of an input date value, and then returns that modified value.

Syntax

Arguments

datepart The part of date to which DATEADD adds an integer number. This table lists all valid datepart arguments.

number An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD will truncate a specified number value that has a decimal fraction. It will not round the number value in this situation.

date An expression that can resolve to one of the following values:

• date

• datetime

• datetimeoffset

• datetime2

• smalldatetime

• time

For date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues.

Return Types

The return value data type for this method is dynamic. The return type depends on the argument supplied for date. If the value for date is a string literal date, DATEADD returns a datetime value. If another valid input data type is supplied for date, DATEADD returns the same data type. DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part.

Example 1

Incrementing datepart by an interval of 1

Example 2

Incrementing more than one level of datepart in one statement

Each of these statements increments datepart by a number large enough to additionally increment the next higher datepart of date:

Example 3

Using expressions as arguments for the number and date parameters

Specifying a column as date

This example adds 2 (two) days to each value in the OrderDate column, to derive a new column named PromisedShipDate:

EOMONTH

The EOMONTH function returns the last day of the month containing a specified date, with an optional offset.

Syntax

Arguments

start_date A date expression that specifies the date for which to return the last day of the month.

month_to_add An optional integer expression that specifies the number of months to add to start_date.

If the month_to_add argument has a value, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then EOMONTH will raise an error.

Return Types

Remarks

The EOMONTH function can remote to SQL Server 2012 (11.x) servers and higher. It cannot be remote to servers with a version lower than SQL Server 2012 (11.x).

Example 1

EOMONTH with explicit datetime type

Example 2

EOMONTH with string parameter and implicit conversion

Example 3

EOMONTH with and without the month_to_add parameter

SWITCHOFFSET

The SWITCHOFFSEET function returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

Syntax

Arguments