Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
The Cinchy system values covered in this section:
The @cinchy_row_id function returns the cinchy ID of the last-inserted row.
Cinchy Supported Functions covers the following topics:
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.
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.
The SELECT and FROM clause work the same for a table-valued UDF as they would for a regular Cinchy table.
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).
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.
Select a scalar value UDF as a column (Image 1).
Scalar-valued functions have to be invoked with a parameter, even if the definition of the function doesn't require a parameter. You can pass a string:
SELECT my_scalar('a') FROM [Cinchy].[Tables] WHERE [Deleted] IS NULL AND [Cinchy Id]=1
Once you confirm that the UDF returns the expected result though a query, a calculated column can be include.
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).
To use the UDF to trigger an action (such as creating a row in another table), it should be a cached calculated column.
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.
The Cinchy functions covered in this section are:
Use the resolveLink function to insert or update values for link columns. Use it with values in the target table or the Cinchy Id.
Using resolveLink against non-unique data values may not return the same data response each time; in that scenario we recommend that you resolve using the CinchyID instead.
The currentUserID() function returns the currently logged in user's Cinchy Id. This is useful for setting up views as well as permissions.
You can use the below example in a view filter so that only the currently logged in user's tasks will appear.
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.
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.
To add a CacheTimeout, enter the number of seconds as a 3rd parameter to the function.
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.
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.
This example will return the CinchyID of the user who last modified the Name column in the Employees table.
This function queries for draft values on tables where Change Approval is enabled.
When querying for draft data, the query result type needs to be set to "Query Results (including Draft Data)"
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):
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.
If you are having issues with your script, try pasting it into JSHint, a tool that helps to detect errors and potential problems in your JavaScript code, as it also runs ECMAScript 5.1.
All UDFs are registered in the Cinchy User Defined Functions table (Image 1).
Don't name UDFs the same names as SQL or CQL functions. Doing so may cause your platform to break. For example, don't name your UDF CONCAT.
All Cinchy UDFs use JavaScript, and follow the convention below:
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.
To use advanced functions in UDFs, import the following:
You can use the following functions in a Cinchy UDF (but not in CQL directly).
You can use XMLHttpRequest()
helper to help POST or GET data from an external API. You can also access Cinchy basicAuthAPIs this way.
You can execute a Cinchy query or a non query (not expecting a result back) in a UDF.
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 returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
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.
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.
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.
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.
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.
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:
This function is used with CONVERT to convert an expression of one data type to another.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
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.
Returns expression
, translated to data_type
This function is used with CAST to convert an expression of one data type to another.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
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.
Value | Definiton |
---|---|
Column Name | Description |
---|---|
Method | Description |
---|---|
Method | Description |
---|---|
Data type | Format | Range | Accuracy | Storage size (bytes) | User-defined fractional second precision | Time zone offset |
---|
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see .
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see .
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see .
You can review the full list of in-progress function translations.
You can review the full list of in-progress function translations.
Without century | With century | Input/Output | Standard |
---|
Value | Explanation |
---|
Value | Explanation |
---|
Function Name
GetLastModifiedBy
Function Description
This function will return the CinchyID of the user who last modified the specified column.
Function Type
Scalar
Return Type
Numeric. It returns the CinchyID
Name
The Name column contains the name of the User Defined Function.
Script
The Script column can contain any number of JavaScript functions that are necessary and referenced by the single function that's registered in the Name column
Open
This creates the HttpClient()
.
setRequestHeader
This adds the header to the client.
Send
This uses the client to call Get()
.
Send (postdata)
This uses the client to call POST, PUT, etc.
Status
This is attributed to show the status of a client response.
responseText
This is attributed to show the response text after the client is called.
executeNonQuery
This is used for INSERTS, DELETES, and UPDATES. It returns a Long value.
executeQuery
This is used for SELECT
statement. It returns system.data values.
executeBatchUpsert
This performs a batch UPSERT into Cinchy. It returns int values.
execute
This returns a queryResult
object that has additional information about your query. The final parameter in execute determines whether it's a query or non query. true = queryfalse
= non query. For more information, see the queryResult example below.
time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | Yes | No |
date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 | No | No |
smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 | No | No |
datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 | No | No |
datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 | Yes | No |
datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 | Yes | |
0 | 100 | mon dd yyyy hh:miAM/PM | Default |
1 | 101 | mm/dd/yyyy | US |
2 | 102 | yyyy.mm.dd | ANSI |
3 | 103 | dd/mm/yyyy | British/French |
4 | 104 | dd.mm.yyyy | German |
5 | 105 | dd-mm-yyyy | Italian |
6 | 106 | dd mon yyyy | - |
7 | 107 | Mon dd, yyyy | - |
8 | 108 | hh:mm:ss | - |
9 | 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) | Default + millisec |
10 | 110 | mm-dd-yyyy | USA |
11 | 111 | yyyy/mm/dd | Japan |
12 | 112 | yyyymmdd | ISO |
13 | 113 | dd mon yyyy hh:mi:ss:mmm | Europe (24 hour clock) |
14 | 114 | hh:mi:ss:mmm | 24 hour clock |
20 | 120 | yyyy-mm-dd hh:mi:ss | ODBC canonical (24 hour clock) |
21 | 121 | yyyy-mm-dd hh:mi:ss.mmm | ODBC canonical (24 hour clock) |
- | 126 | yyyy-mm-ddThh:mi:ss.mmm | ISO8601 |
- | 127 | yyyy-mm-ddThh:mi:ss.mmmZ | ISO8061 (with time zone Z) |
- | 130 | dd mon yyyy hh:mi:ss:mmmAM | Hijiri |
- | 131 | dd/mm/yy hh:mi:ss:mmmAM | Hijiri |
#### Converting float to real |
0 | Maximum 6 digits (default) |
1 | 8 digits |
2 | 16 digits |
0 | No comma delimiters, 2 digits to the right of decimal |
1 | Comma delimiters, 2 digits to the right of decimal |
2 | No comma delimiters, 4 digits to the right of decimal |
The validate date and time value function covered in this section is:
ISDATE checks an expression to see if it's correct.
It will return 1 if the expression is a valid date, time, or datetime value; otherwise, it will return 0. ISDATE will also return 0 if the expression is a datetime2 value.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
expression
Is a character string or expression that can be converted to a character string. The expression must be less than 4,000 characters. Date and time data types, except datetime and smalldatetime, aren't allowed as the argument for ISDATE
.
ISDATE
is deterministic only when used with the CONVERT
function, if the CONVERT
style parameter is specified, and style isn't equal to 0, 100, 9, or 109.
The return value of ISDATE
depends on the settings set by SET DATEFORMAT
, SET LANGUAGE
and Configure the default language Server Configuration Option.
Using ISDATE to Test Valid datetime Expression
The return date and time difference value functions covered in this section are:
The DATDIFF
function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
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.
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.
Finding the number of days between two dates.
Specifying user-defined variables for startdate and enddate
Example 3: Specifying scalar system functions for startdate and enddate
`DATEIFF_BIG function returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
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.
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.
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.
The logical functions covered in this section are:
The CHOOSE function returns an item at the specified index from a list of values in Cinchy.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
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.
Returns the data type with the highest precedence from the set of types passed to the function.
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.
Simple CHOOSE example
Simple CHOOSE example based on column
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
.
IFF returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform.
New function translations are actively being worked on by the development team; please check back at a later time.
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.
Returns the data type with the highest precedence from the types in true_value
and false_value
.
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.
Return 5 if the condition is TRUE, or 10 if the condition is FALSE:
You can review the full list of in-progress function translations.
You can review the full list of in-progress function translations.