Overview

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

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

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

Data Manipulation Language (DML)

List of available DML statements:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• ​DECLARE​

• SET

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​

Overview

Use Data Manipulation Language (DML) to add, retrieve, update and manipulate data. The Cinchy DML statements covered on this page are:

• ​SELECT​

• ​INSERT​

• ​UPDATE​

• ​DELETE​

• ​IF​

• DECLARE​

• ​SET​

SELECT

Use the SELECT statement 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]

Produce a nested JSON

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

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)

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

Use an INSERT statement 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.

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

Use the UPDATE statement to change the data in a table. The UPDATE statement modifies zero or more rows of a table, depending on how many rows meet the search condition specified in the WHERE clause. You can also use an UPDATE statement to specify the values to be updated in a single row. To do this, specify the constants, host variables, expressions, DEFAULT, or NULL. Specify NULL to remove a value from a row's column (without removing the row).

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

Use the DELETE statement to remove entire rows from a table. The number of rows deleted depends on how many rows match the search condition specified in the WHERE statement.

Syntax

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

Example

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

IF

Use the IF statement to execute a condition. If the condition is satisfied, then the Boolean expressions returns TRUE value. The optional ELSE keyword introduces another statement that executes when the IF condition isn't 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

Use the DECLARE statement to declare a variable.

Syntax

DECLARE @variable_name variable_type;

Example

DECLARE @var varchar(50);

SET

Use the SET variable to assign a value to a variable.

Syntax

SET @variable_name = variable_value

Example

SET @var = 'Alfreds Futterkiste'

Overview

Cinchy Supported Functions covers the following topics:

• ​Cinchy Functions​

• ​Cinchy System Values​

• ​Cinchy User Defined Functions​

• Scalar Functions

Cinchy Functions and Cinchy User Defined Functions (UDFs) are used in conjunction with Cinchy DML and DDL 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.

​

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.

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'

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.

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:

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

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 data type the column can hold (such as varchar, char, int, date).

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 has rows and columns, taken from fields of an original table and presents them with a different structure and organization. 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, including indexed views. ALTER VIEW doesn't affect dependent stored procedures or triggers and doesn't 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

Use the DROP VIEW command to delete a view.

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]

Overview

The Cinchy system values covered in this section:

• @cinchy_row_id

@cinchy_row_id

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

Syntax

select @cinchy_row_id

Examples

INSERT INTO [Contacts].[People] ([First Name],[Last Name])
VALUES ('John','Smith')

select @cinchy_row_id

Overview

Similar to the SQL construct of table-valued functions, you can SELECT or CROSS JOIN from a Cinchy UDF as if it's a table.

Use 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

Create a Table in a UDF

To generate a table within a UDF for use in CQL, you need to create a data table 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);
}

Overview

These functions support data type casting and conversion. Conversion functions convert an expression of one data type to another data type. The conversion functions covered in this section are:

• ​CAST​

• ​CONVERT​

CAST

This function is used with CONVERT to convert an expression of one data type to another.

Syntax

CAST ( expression AS data_type [ ( length ) ] )

Arguments

expression Any valid expression

data_type The target data type.

length An optional integer that specifies the length of the target data type, for data types that allow a user specified length.

Return Types

Returns expression, translated to data_type

Example

SELECT CAST(1.5 AS int)

CONVERT

This function is used with CAST to convert an expression of one data type to another.

Example

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments

expression Any valid expression

data_type The target data type.

length An optional integer that specifies the length of the target data type, for data types that allow a user specified length.

style An optional integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Converting datetime to character

Converting money to character

Example

SELECT CONVERT(datetime, '2020-01-01')

Overview

The Cinchy functions covered in this section are:

• ​resolveLink()​

• ​currentUserID()​

• ​currentUsersGroups()​

• ​executeSavedQuery()​

• ​GetLastModifiedBy()​

• ​draft([Column Name])​

resolveLink()

Use the resolveLink function to insert or update values for link columns. Use it with values in the target table or 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. This function has two optional parameters: 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 parameters

Timeout

To add a CacheTimeout, enter the number of seconds as a 3rd parameter to the function.

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

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's 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 queries 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's pending a change request (Image 1).

To return results that include the draft changes in the First Name column, set your 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]

Overview

Similar to the SQL construct of scalar-valued functions, you can use Scalar-valued functions to return a single value of any CQL data type. The function body can execute any JavaScript logic.

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'

Use a Scalar-Valued UDF in a Calculated Column

Once you confirm that the UDF returns the expected result though a query, a calculated column can be include.

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

Trigger an action

To use the UDF to trigger an action (such as creating a row in another table), it should be a cached calculated column.

Trigger best practices

Watch out for the following scenarios:

• Don't trigger when you don't have all the necessary fields.

• Don't trigger when non-relevant data on the row changes.

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

Overview

Cinchy User Defined Functions (UDFs) give customers a way to specify and use more particular logic in your solutions than basic CQL. You can use CQL to simplify calculations and orchestrate automation to accommodate your business requirements.

UDFs are written in JavaScript.

Cinchy UDFs divide into two groups:

• Table-Valued Functions - Similar to the SQL construct of table-valued functions, you can SELECT or CROSS JOIN from a Cinchy UDF as if it's 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.

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

Structure

All Cinchy UDFs use JavaScript, and follow the convention below:

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

UDFs can perform external API calls and execute Cinchy Queries. All UDFs should return an indication of success or failure, even if you don't want to return any values.

You can create helper functions within a UDF, but you can't reference other UDFs.

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

You can use the following functions in a Cinchy UDF (but not in CQL directly).

External API calls

You can use XMLHttpRequest() helper to help POST or GET data from an external API. You can also access Cinchy basicAuthAPIs 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.";
}

Cinchy Query Call

You can execute a Cinchy query or a non query (not expecting a result back) in a UDF.

Supported methods

Examples

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;
}

QueryResult return object

// public class QueryResult
{
	//plain error message
	public string error { get; set; }
	//error with stacktrace
	public string errorDetail { get; set; }
	//data table from Query if returnDataTable is true
	public DataTable table { get; set; }
	//value of num of rows affected if returnDataTable is false
	public long rowsAffected { get; set; }
	//how many ms took to run query
	public double executionTimeMs { get; set; }
}

QueryResult examples

/*function testScalarUdf */
function testScalarUdf(p1, p2) {
  var sysData = importNamespace("System.Data");
  var param = [];

  var dataTable = [];

  var queryResult = Query.execute(
    "SELECT TOP 1 [Username] FROM [Cinchy].[Users] ORDER BY [Cinchy Id]",
    param,
    null,
    null,
    true
  );

  return getSingleValue(queryResult.table, "Username");
}

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;
}

/* end function testScalarUdf */

//CQL
SELECT testScalarUdf(1), [Username]
FROM [Cinchy].[Users]
WHERE [Deleted] IS NULL

/*function testTableUdf */

function testTableUdf(query) {
  var sysData = importNamespace("System.Data");
  var param = [];

  var dataTable = [];

  var queryResult = Query.execute(query, param, null, null, true);

  var singleVal = getSingleValue(queryResult.table, "Username");

  var result = {};
  result["schema"] = [
    {
      columnName: "rowsAffected ",
      type: "String",
    },
    {
      columnName: "UserName",
      type: "String",
    },
    {
      columnName: "error ",
      type: "String",
    },
    {
      columnName: "errorDetail ",
      type: "String",
    },
    {
      columnName: "executionTimeMs",
      type: "String",
    },
  ];
  result["data"] = [];
  result["data"].push([
    queryResult.rowsAffected,
    singleVal,
    queryResult.error,
    queryResult.errorDetail,
    queryResult.executionTimeMs,
  ]);
  return JSON.stringify(result);
}

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

/* end function testTableUdf */

//CQL
SELECT t.*
FROM testTableUdf('SELECT TOP 1 [Username] FROM [Cinchy].[Users] ORDER BY [Andrew]') t

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 Function Categories

  • ​

  • ​

  • ​

Date and Time Data Types

​

Overview

The following return date and time values from their parts functions covered in this section are:

DATEFROMPARTS

This function returns a date value that maps to the specified year, month, and day values.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month, from 1 to 12.

day An integer expression that specifies a day.

Return types

Remarks

DATEFROMPARTS returns a date value, with the date portion set to the specified year, month and day, and the time portion set to the default. For invalid arguments, DATEFROMPARTS will raise an error. DATEFROMPARTS returns null if at least one required argument has a null value.

Example

DATETIME2FROMPARTS

DATETIME2FROMPARTS function returns a datetime2 value for the specified date and time arguments. The returned value has a precision specified by the precision argument.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies the hours.

minute An integer expression that specifies the minutes.

seconds An integer expression that specifies the seconds.

fractions An integer expression that specifies a fractional seconds value.

percision An integer expression that specifies the precision of the datetime2 value that DATETIME2FROMPARTS will return.

Return types

Remarks

DATETIME2FROMPARTS returns a fully initialized datetime2 value. DATETIME2FROMPARTS will raise an error if at least one required argument has an invalid value. DATETIME2FROMPARTS returns null if at least one required argument has a null value. However, if the precision argument has a null value, DATETIME2FROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIME2FROMPARTS will raise an error.

Example 1: Without fractions of a second

Example 2: With fractions of a second

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.

2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.

3. When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

DATETIMEFROMPARTS

DATETIMEFROMPARTS function returns a datetime value for the specified date and time arguments.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

milliseconds An integer expression that specifies milliseconds.

Return types

Remarks

DATETIMEFROMPARTS returns a fully initialized datetime value. DATETIMEFROMPARTS will raise an error if at least one required argument has an invalid value. DATETIMEFROMPARTS returns null if at least one required argument has a null value.

Example

DATETIMEOFFSETFROMPARTS

Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument and an offset as specified by the offset arguments.

Syntax

Arguments

year An integer expression that specifies a year.

month An integer expression that specifies a month.

day An integer expression that specifies a day.

hour An integer expression that specifies hours.

minute An integer expression that specifies minutes.

seconds An integer expression that specifies seconds.

frcations An integer expression that specifies a fractional seconds value.

hour_offset An integer expression that specifies the hour portion of the time zone offset.

minute_offset An integer expression that specifies the minute portion of the time zone offset.

precision An integer literal value that specifies the precision of the datetimeoffset value that DATETIMEOFFSETFROMPARTS will return.

Return types

Remarks

DATETIMEOFFSETFROMPARTS returns a fully initialized datetimeoffset data type. The offset arguments represent the time zone offset. For omitted offset arguments, DATETIMEOFFSETFROMPARTS assumes a time zone offset of 00:00 - in other words, no time zone offset. For specified offset arguments, DATETIMEOFFSETFROMPARTS expects values for both arguments, and both values positive or negative. If minute_offset has a value and hour_offset has no value, DATETIMEOFFSETFROMPARTS will raise an error. DATETIMEOFFSETFROMPARTS will raise an error if the other arguments have invalid values. If at least one required arguments have a NULL value, then DATETIMEOFFSETFROMPARTS will return NULL. However, if the precision argument has a NULL value, then DATETIMEOFFSETFROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIMEOFFSETFROMPARTS will raise an error.

Example 1: Without fractions of a second

Example 2: With fractions of a second

This example shows the use of the fractions and precision parameters:

1. When fractions have a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.

2. When fractions have a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.

3. When fractions have a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS returns a smalldatetime value for the specified date and time.

Syntax

Arguments

year Integer expression specifying a year.

month Integer expression specifying a month.

day Integer expression specifying a day.

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

Return types

Remarks

This function acts as a constructor for a fully initialized smalldatetime value. If the arguments aren't valid, then an error is thrown. If required arguments are null, then null is returned.

Example

TIMEFROMPARTS

TIMEFROMPARTS returns a time value for the specified time and with the specified precision.

Syntax

Arguments

hour Integer expression specifying hours.

minute Integer expression specifying minutes.

seconds Integer expression specifying seconds.

fractions Integer expression specifying fractions.

precision Integer literal specifying the precision of the time value to be returned.

Return types

Remarks

TIMEROMPARTS returns a fully initialized time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

The fractions argument depends on the precision argument. For example, if precision is 7, then each fraction represents 100 nanoseconds; if precision is 3, then each fraction represents a millisecond. If the value of precision is zero, then the value of fractions must also be zero; otherwise, an error is raised.

Example 1: Without fractions of a second

Example 2: With fractions of a second

The following example demonstrates the use of the fractions and precision parameters:

1. When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

2. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

3. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

Overview

SQL derives all system date and time values from the operating system of the computer on which the instance of SQL Server runs.

SQL Server 2019 (15.x) derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server running. This API has a precision fixed at 100 nanoseconds. Use the GetSystemTimeAdjustment() Windows API to determine the accuracy. The return system date and time value functions covered in this section are:

• ​

SYSDATETIME

SYSDATETIME returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

Syntax

Return Types

Remarks

SQL statements can refer to SYSDATETIME anywhere they can refer to a datetime2(7) expression.

SYSDATETIME is a nondeterministic function. You can't index views and expressions that reference this function.

Example 1: Get the current System Date and Time

Example 2: Get the current System Date

Example 3: Get the current System Time

SYSDATETIMEOFFSET

SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

Syntax

Return Types

Remarks

SQL statements can refer to SYSDATETIMEOFFSET anywhere they can refer to a datetimeoffset expression.

SYSDATETIMEOFFSET is a nondeterministic function. You can't index views and expressions that reference this function.

Example 1: Show formats Returned by the Date and Time Functions

Example 2: Convert Date and Time to Date

Example 3: Convert Date and Time to Times

SYSUTCDATETIME

SYSUTCDATETIME returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time are returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.

Syntax

Return types

Remarks

SQL statements can refer to SYSUTCDATETIME anywhere they can refer to a datetime2 expression.

SYSUTCDATETIME is a nondeterministic function. You can't index views and expressions that reference this function.

Example 1: Show formats returned by Date and Time functions

Example 2: Convert Date and Time to Date

Example 3: Convert Date and Time to Time

Overview

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

DATEDIFF

The DATDIFF function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

Syntax

Arguments

datepart The units in which DATEDIFF reports the difference between the startdate and enddate. Commonly used datepart units include month or second.

The datepart value can't be specified in a variable, nor as a quoted string like 'month'.

The following table lists all the valid datepart values. DATEDIFF accepts either the full name of the datepart, or any listed abbreviation of the full name.

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

• date

• datetime

• datetimeoffset

• smalldatetime

• time

Use four-digit years to avoid ambiguity.

Return Types

Remarks

Use DATEDIFF in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF implicitly casts string literals as a datetime2 type. This means that DATEDIFF doesn't 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.

Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF may overflow with a precision of minute or higher if the difference between enddate and startdate returns a value that's out of range for int.

Example 1

Finding the number of days between two dates.

Example 2

Specifying user-defined variables for startdate and enddate

Example 3: Specifying scalar system functions for startdate and enddate

DATEDIFF_BIG

`DATEIFF_BIG function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.

Syntax

Arguments

datepart The part of startdate and enddate that specifies the type of boundary crossed.

This table lists all valid datepart argument names and abbreviations.

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

• date

• datetime

• datetimeoffset

• smalldatetime

• time

For date, DATEDIFF_BIG 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. DATEDIFF_BIG subtracts startdate from enddate. To avoid ambiguity, use four-digit years.

Return Types

Remarks

Use DATEDIFF_BIG in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

DATEDIFF_BIG implicitly casts string literals as a datetime2 type. This means that DATEDIFF_BIG doesn't 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.

Specifying SET DATEFIRST has no effect on DATEDIFF_BIG. DATEDIFF_BIG always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

DATEDIFF_BIG may overflow with a precision of nanosecond if the difference between enddate and startdate returns a value that's out of range for BigInt.

Example

This example uses different types of expressions as arguments for the startdate and enddate parameters. It calculates the number of day boundaries crossed between dates in two columns of a table.

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 won't 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 can't 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's changed from the stored time zone offset to a specified new time zone offset.

Syntax

Arguments

DATETIMEOFFSETDATETIMEOFFSET Is an expression that can be resolved to a datetimeoffset(n) value.

time_zone Is a character string in the format [+|-]TZH:TZM or a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted.

Return Types

datetimeoffset with the fractional precision of the DATETIMEOFFSET argument.

Remarks

Use SWITCHOFFSET to select a datetimeoffset value into a time zone offset that's different from the time zone offset that was originally stored. SWITCHOFFSET doesn't update the stored time_zone value.

SWITCHOFFSET can be used to update a datetimeoffset column.

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan.

Example

TODATETIMEOFFSET

TODATETIMEOFFSET function returns a datetimeoffset value that's translated from a datetime2 expression.

### Syntax

Arguments

expression Is an expression that resolves to a datetime2 value.

time_zone Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string), for example '+13:00'. The range is +14 to —14 (in hours). The expression is interpreted in local time for the specified time_zone.

Return Types

datetimeoffset. The fractional precision is the same as the datetime argument.

Example 1

Changing the time zone offset of the current date and time

The following example changes the zone offset of the current date and time to time zone -07:00.

Example 2

Changing the time zone offset in minutes

The following example changes the current time zone to -120 minutes.

Example 3

Adding a 13-hour time zone offset

The following example adds a 13-hour time zone offset to a date and time.

Overview

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

DATENAME

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

Syntax

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

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

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

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 doesn't 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

This example returns the Base Year.

Example 2

This example returns the Day part of the Date.

Example 3

This example returns the Year part of the Date.

DAY

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

Syntax

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

Example 1

This returns 30 - the number of the day itself

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.

MONTH

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

Syntax

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

Example 1

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

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.

YEAR

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

Syntax

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

Example 1:

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

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.

Overview

With Cinchy, you can now create spatial tables with geometry and geography data sets, and blend this with other sources to unlock more intelligence from your fabric.

You can use this data to support an endless number of use-cases that rely on location and mapping data to help your teams make the most informed decisions.

The geometry and geography functions divide into the following pages:

Overview

The logical functions covered in this section are:

• ​

CHOOSE

The CHOOSE function returns an item at the specified index from a list of values in Cinchy.

Syntax

Arguments

index Is an integer expression that represents a 1-based index into the list of the items following it.

If the provided index value has a numeric data type other than int, then the value is implicitly converted to an integer. If the index value exceeds the bounds of the array of values, then CHOOSE returns null.

Return Types

Returns the data type with the highest precedence from the set of types passed to the function.

Remarks

CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

Example 1

Simple CHOOSE example

Example 2

Simple CHOOSE example based on column

Example 3

CHOOSE in combination with MONTH

The following example returns the season in which a user was added to Cinchy. The MONTH function is used to return the month value from the column HireDate.

IIF

IFF returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy.

Syntax

Arguments

boolean_expression A valid Boolean expression.

If this argument isn't a Boolean expression, then a syntax error is raised.

true_value Value to return if boolean_expression evaluates to true.

false_value Value to return if boolean_expression evaluates to false.

Return Types

Returns the data type with the highest precedence from the types in true_value and false_value.

Remarks

IIF is a second version of writing a CASE expression. It evaluates the Boolean expression which was passed as the first argument and then returns either TRUE or FALSE based on the result of the evaluation. The true_value is returned if the Boolean expression is TRUE, and the false_value is returned if the Boolean expression is FALSE or unknown.

Example

Return 5 if the condition is TRUE, or 10 if the condition is FALSE:

Overview

The string functions covered in this section perform operations on a string (char or varchar) input value and return a string or numeric value.

ASCII

ASCII (American Standard Code for Information Interchange) returns the ASCII code value of the leftmost character of a character expression.‌

Syntax

Example

CHAR

This function converts an int between 0 to 255 to a character value. Outside of this range, the CHAR function will return a NULL value.‌

Syntax

Arguments

integer_expression

An integer from 0 through 255.

Return types

char(1)

Example

CHARINDEX

This function searches for one character expression inside another character string. If found, the function will return the starting position of the first expression.‌

Syntax

Arguments

expressionToFind The expression that needs to be found in the ExpressionString

expressionString The string that contains the expression

start_Location Start location from where the search will start

If CHARINDEX doesn't find expressionToFind within expressionString, CHARINDEX will return 0.

Example 1

Returning the starting position of an expression‌

This example searches for a in the string value.

Example 2

Returning the starting position of an expression with an optional start location‌

This example searches for a in the string value starting from 15th position.

CONCAT

The CONCAT function concatenates two or more string values one after the other. This function requires at least 2 strings and no more than 254 strings to concatenate.‌

Syntax

Arguments

string A string to concatenate to the other strings.

Return types

string A string with all the concatenated strings.

Example

DIFFERENCE

This function returns an integer value measuring the difference between the SOUNDEX () values of two different character expressions.strings.‌

Syntax

Arguments

string

An alphanumeric expression of character data. string can be a constant, variable, or column.‌

Return types

int

Example

FORMAT

Returns a value formatted with the specified format. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.‌

Syntax

Arguments

value

Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.‌

format

nvarchar format pattern.‌

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, YYYY (DDDD)").

Return types

nvarchar or null‌

The length of the return value is determined by the format.‌

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

Example 1

FORMAT with strings‌

The following example shows formatting date values by specifying a custom format.

Example 2

FORMAT with numeric values.

The following example shows formatting numeric values by specifying a custom format.

LEFT

Returns the left part of a character string with the specified number of characters.‌

Syntax

Arguments

string

Is an expression of character or binary data. It can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert string.‌

integer

Is a positive integer that specifies how many characters of the string will be returned.

Return types

Returns a string ‌

Example

The following example returns the two leftmost characters from the string.

LEN

Returns the number of characters of the specified string expression, excluding trailing spaces.‌

Syntax

Arguments

string

Is the string expression.‌

Return types

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.‌

Example

The following example selects the number of characters and the data in string abcde.

LOWER

Returns a character expression after converting uppercase character data to lowercase.‌

Syntax

Arguments

string

Is an expression of character or binary data.

Return types

varchar or nvarchar‌

Example

The following example uses the LOWER function.

LTRIM

Returns a character expression after it removes leading blanks.‌

Syntax

Arguments

string

Is an expression of character or binary data.‌

Return types

varchar or nvarchar‌

Example: Using LTRIM

The following example uses LTRIM to remove leading spaces from a string

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression.‌

Syntax

Arguments

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern.

expression

Is an expression, typically a column that's searched for the specified pattern. expression is of the string data type category.‌

Return types

int or bigint

Example

The following example checks a short character string (this is a great day) for the starting location of the characters eat.

REPLACE

Replaces all occurrences of a specified string value with another string value.‌

Syntax

Arguments

string

Is the string expression to be searched.‌

string_toBeReplaced

Is the string to be found in the string.

string_replacedBy

Is the replacement string.

Return types

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.‌

Example

The following example replaces the string cde in abcdefghi with xyz.

REVERSE

Returns the reverse order of a string value.‌

Syntax

Arguments

string

It's an expression of a string or binary data type.

Return types

varchar or nvarchar‌

Example