This is the full alphabetical list of Cirro SQL and supported SQL-92.

The following pages and posts are tagged with

TitleTypeSummary
ABS ABSVAL Page ABS and ABSVAL are both supported, perform the same function, and can be used interchangeably. They returns the absolute value of a number in the same data ...
ACOS Page Returns the arc cosine of a numeric value as a DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
ADD AUTHENTICATION PROVIDER Page ADD or ALTER AUTHENTICATION allows an admin to add external authentication providers like Okta and Azure AD to Cirro.
ADD AUTHORIZATION Page Associates an existing Cirro user (created with CREATE USER) with an existing account on the federated system.
ADD RESOURCE Page Link a driver file with a resource created with CREATE DRIVER.
Addition Page Adds two numeric values.
ALTER ALERT ACTION Page An ALERT is a notification that's triggered when a CONNECTION RULE is met. They can be email or HTTP actions.
ALTER CONNECTION RULE Page Alter a connection rule using SQL.
ALTER DRIVER Page Allows modification of an existing driver, which was created previously with CREATE DRIVER.
ALTER ENCRYPTION KEY Page Use ALTER ENCRYPTION KEY to make changes to an existing key, with the same syntax.
ALTER METADATA Page Overrides the internal rowcount for a table or view. This is a global setting, and will affect all users and all sessions for this system.
ALTER POLICY (Set User Security Policy) Page SQL commands to set security policies for standard passwords such as username length and password length
ALTER PROJECT Page Alter a Data project with this syntax.
ALTER PROJECT MAP Page Alter a project map with these SQL commands.
ALTER SYSTEM Page Allows modification of an existing system, which was created with CREATE SYSTEM.
ALTER TABLE Page Alter a table on a specified datasource.
ALTER USER Page Make alterations to existing Cirro user accounts. All Cirro users have the PUBLIC role by default which enables login.
ALTER VIEW Page Alters the SELECT query of an existing, non-materialized view.
ALTER VIEW - ADD/DROP PROJECTION Page View projections are Cirro's implementation of row and column security. They allow the same view to return different data depending on who selects from them.
ASIN Page Returns the arc sine of a numeric value as A DOUBLE value. Returns NULL if numValue is NULL. Returns 0 if numValue is 0. Returns an error if numValue is less...
ATAN2 Page Returns the arc tangent of the quotient of two arguments.
ATAN Page Returns the arc tangent of a numeric value as DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
AVG Page AVG returns the aggregate average of values.
Begin End block syntax Page Begin---End is used in Data Projects for data movement and parallelization.
BETWEEN Page Determines whether a value is within a specified range defined by two boundaries.
BIGINT Data Type Page BIGINT is an Integer numeric type data type
BIGINT Page Use of this function converts a value to a BIGINT.
CALL Page Executes a pre-defined procedure.
CANCEL PROJECT Page The CANCEL PROJECT statement will terminate a running project, i.e. find the session and cancel its statement. This requires the CANCEL SESSION privilege.
CASE Page The CASE statement is used in place of an expression to return a value based on other values in the query.
CAST Page CAST converts a value from one data type to another.
CEIL Page Returns the smallest whole number that is greater than or equal to the parameter.
CHAR Data Type Page CHAR is a fixed length string type data type.
CHAR Page CHAR converts a value to CHAR.
CHAR FOR BIT Data Type Page CHAR FOR BIT is a fixed length Binary Data Type.
CHAR_LENGTH/LENGTH Page CHAR_LENGTH/LENGTH Returns the length of a string value, including spaces and special characters.
CHECKSUM and ROWCHECKSUM Page CHECKSUM returns a N-bit checksum that returns the same result for the same data on different RDBMS'. It has the same syntax and arguments as ROWCHECKSUM.
CLEAR CACHE Page Empties the system cache, or the given cache ID.
COALESCE Page COALESCE returns the first of multiple arguments that is not NULL.
COMPARE_ENGINE Page COMPARE_ENGINE is used to define the type of processing used in a data project.
COMPARE as inline function Page COMPARE can be combined with inline functions to do simpler, hopefully faster, compares..
COMPARE summary Page This page gives background information on Compare syntax.
COMPARE Page Compare the rows of the target cursor or table to those of the source cursor or table.
COMPARE_WRITERS Page Sets the number of compare log writers (when not single-threaded). Default value is 2.
Concatenation Page Concatenates two string values.
CONVERT Page Use this function to convert a value from one data type to another.
COPY OBJECTS Page Use COPY OBJECTS to define source to target column mapping where the number of columns differs.
COT Page Returns the cotangent of a numeric value.
COUNT Page COUNT Returns a count of values in the data set.
CREATE ALERT ACTION Page An ALERT ACTION is a notification that's triggered when a CONNECTION Rule is met. They can be email or HTTP actions.
CREATE CONNECTION RULE Page Add a CONNECTION RULE to allow or restrict access to Cirro either before or after login.
CREATE DRIVER Page Creating a Cirro driver allows you to connect to a data source. After the driver has been created, add resources to it with the ADD RESOURCE command.
CREATE ENCRYPTION KEY Page CREATE ENCRYPTION KEY creates an encryption key within the system, saving it to a specified location.
CREATE JOB Page Create Job inserts scripts into the scheduler.
CREATE PROJECT Page Create a Data project with this syntax.
CREATE REMOTE DATABASE Page Creates a local synonym for a remote database. This functionality is used to create mappings to the four-part object names required by Cirro.
CREATE REMOTE Page Creates a Cirro function reference which maps to an existing function or functions on remote systems.
CREATE ROLE Page Adds a new Cirro role with optional LDAP/Active Directory integration.
CREATE SYSTEM Page These commands link Cirro to the target database. Each distinct Database type should be added with its own CREATE SYSTEM command.
CREATE TABLE Page Create a table on a specified datasource. If the datasource is not specified, Cirro identifies the most efficient datasource to create the table.
CREATE TABLE as SELECT Page Alters the SELECT query of an existing, non-materialized view.
CREATE USER Page Create a Cirro user account that can connect to the application. All Cirro users have the PUBLIC role by default which enables login. Once created, GRANT rol...
CREATE VIEW Page Creates a materialized or non-materialized view from existing tables or views.
CURRENT DATE Page URRENT DATE function returns the current date on the Cirro Data Hub.
CURRENT TIME Page Returns the current time on the Cirro Data Hub.
CURRENT TIMESTAMP Page CURRENT TIMESTAMP function returns a timestamp the current day and time on the Cirro Data Hub.
USER / CURRENT_USER Page Returns the Cirro username used when connecting to Cirro.
CURRENT_UTC_DATE Page CURRENT_UTC_DATE function returns the system date in UTC (Coordinated Universal Time).
CURRENT_UTC_TIME Page CURRENT_UTC_TIME function returns the system time in UTC (Coordinated Universal Time).
CURRENT_UTC_TIMESTAMP Page CURRENT_UTC_TIMESTAMP functions returns the system timestamp in UTC (Coordinated Universal Time).
Data Project Use Cases Page Learn different ways to copy data from a source to a target database.
DATACOPY Page SQL to copy data from source to target.
DATE Data Type Page The DATE Data Type stores date values without time components.
DATE Page Use this function to convert a non-date value to a DATE type
DATE_TRUNC Page DATE_TRUNC truncates a TIMESTAMP to the specified precision.
DATEADD Page DATEADD adds a specified interval to a date or time value.
DATEDIFF Page DATEDIFF adds a specified interval to a date or time value.
DATENAME Page DATENAME returns the name or numeric value for the specified date component.
DATEPART Page DATEPART returns the numeric portion of a date.
DAY Page DAY returns the day portion of a value.
DAYOFWEEK Page DAYOFWEEK returns the day of the week, as an integer from 1 to 7, where 1 is Sunday.
DAYOFYEAR Page DAYOFYEAR returns the day of the year, as an integer from 1 to 366, where 1 is January 1.
DECIMAL/NUMERIC Data Type Page The DECIMAL/NUMERIC data type is an Exact numeric type datatype
DECRYPT data Page Use an encryption key to decrypt table data.
DEDUP Page DEDUP deletes all but the first row for each key value set, sorted on the key columns and then also the non-key columns.
DEGREES Page Performs an inexact conversion of a number from radians to degrees.
DENY privilege Page Explicitly denies a permission to a user or role. When a DENY is in place, it generally overrides an explicit or inherited GRANT at the same level.
DISABLE USER Page SQL command disables a user account and prevents login. Password reset enables the account once more, once user has chosen a new password.
Division Page Divides one numeric value into another numeric value.
DOUBLE Data Type Page The DECIMAL/NUMERIC data type is a Floating Point numeric type datatype
DOUBLE Page Converts a value to a DOUBLE type.
Data Mapping Page Use Data Mapping to map source objects to targets in any data copy, copy and validate, validate or synchronize data project.
Data Project Scripts Page Cirro Data Projects contain three scripts, pre execution scripts, the main script to perform the data project and post scripts to do any cleanup tasks.
DROP ALERT Page Use the DROP command to delete an alert.
DROP AUTHENTICATION PROVIDER Page Drops authentication provider from Cirro.
DROP AUTHORIZATION Page Drops existing authorization for a user.
DROP CONNECTION RULE Page Delete connection rules.
DROP DEFAULT AUTHENTICATION PROVIDER Page Drop a default authentication provider from the system
DROP DRIVER Page Removes an existing Cirro driver, which was created using the CREATE DRIVER command.
Drop Encryption Key Page DROP ENCRYPTION KEY deletes a named encryption key from Cirro.
DROP JOB Page Drops an existing scheduled job.
DROP PROJECT Page DROP PROJECT changes the project's status flag to INACTIVE. Inactive projects will not usually be displayed. This is to maintain history of project runs unti...
DROP REMOTE DATABASE Page Drops an existing remote database reference.
DROP_REMOTE_FUNCTION Page Shows the expected data movements and supporting queries for a user-specified query, in the expected execution order.
DROP RESOURCE Page Removes an existing Cirro resource, which was added with the ADD RESOURCE command.
DROP ROLE Page Drops an existing Cirro role.
DROP SYSTEM Page Removes an existing Cirro system, which was created using CREATE SYSTEM.
DROP TABLE Page Drops a table which created using the Cirro CREATE TABLE command.
DROP USER Page Drops an existing Cirro user.
DROP VIEW Page Drops an existing view.
DUPLICATES Page The DUPLICATES command identifies all rows in the target table or cursor whose key also appears in other rows.
ENCRYPT data Page Use an encryption key to encrypt table data.
Equals Page Compares two expressions for equivalence, and returns a true value if they are found to be equal. Syntax
EXCEPT and INTERSECT Page Compares two queries and returns distinct rows only based on the keyword provided.
EXCLUDE Schemas Page You can choose to exclude datasource schemas so they are invisible to SQL statements.
EXECUTE PROJECT Page Execute/run the specified data project. EXECUTE PROJECT executes in the current session synchronously.
EXP Page Calculates e (the base of the natural logarithms) raised to the value of the argument.
EXPAND Page Use the statement to EXPAND any SELECT statement into block form.
EXPLAIN Utility Page Shows the expected data movements and supporting queries for a user-specified SELECT query, in the expected execution order.
Oracle issue querying all objects view Page When querying Oracle all_objects view, Cirro performance degrades markedly.
FLOAT Data Type Page The FLOAT data type is a Floating Point numeric type datatype.
FLOOR Page Returns the largest whole number that is less than or equal to the parameter.
FORCE_BATCHSIZE Page Gives the agent used to write data a hint as to the number of rows that should be inserted into the database in each batch. Batching the inserts reduces the ...
Force Column Data Type Hint Page Forces the data type during data movement for a particular column to be one of the Cirro data types or a data type native to a specific database in the feder...
FORCE_FETCHSIZE Page Gives the JDBC driver used to read data a hint as to the number of rows that should be fetched from the database when more rows are needed. If this number is...
Force Rowcount Hint Page Allows the user to override the rowcount used by the query optimizer, with the goal of altering data movement behaviors. The Force Rowcount hint can be appli...
Force Runmode Hint Page Forces the optimizer to move query data to and then execute the query on a user-specified system rather than a system selected by the optimizer.
FORCE_SORT Page Used only for SYNC TO. Use the sorted or unsorted compare engine.
FORCE SPLIT COLUMN Hint Page Forces the optimizer to split data retrieval using a specified column. This parallelizes the query and data movement.
FORCE_TRANSFERTHREADS Page FORCE_TRANSFERTHREADS is one of the original Cirro hints and retains that meaning.
GENERATE PASSWORD Page Generates a system-valid password which can be inserted into any CREATE or ALTER USER statement.
GRANT privilege Page Grants privileges on a specified securable to a user or role.
GRANT KEY USAGE Page Grant SELECT on an encryption key to users or roles.
GRANT role Page Adds a role to a Cirro user or role. The user must be previously granted 'GRANT ROLE' permission.
Greater Than Page Compares two expressions, and returns true if the first value is greater than the second value.
Greater Than or Equal To Page Compares two expressions, and returns true if the first value is greater than or equal to the second value.
Accessing Hadoop and Running Cirro Functions through SQL Page Access these functions to query Hadoop data and perform data manipulations.
HASHROW Page Returns the hexadecimal row hash value of one or more expressions.
HOUR Page HOUR returns the hour portion of a value.
Identifiers and Reserved words Page These words are reserved in either Cirro or the SQL-92 standard and should not be used as identifier names.
IN Page Determines whether a value is within a specified range, defined by a subquery, expression, or list.
INCLUDE Schemas Page Cancel an EXCLUDE SCHEMA statement with INCLUDE to restore datasource schemas for SQL statements.
INSERT Page Adds data into an existing table on the federated system.
INTEGER Data Type Page The INTEGER data type is a Floating Point numeric type datatype.
INTEGER Page Use this function to convert a value to an INTEGER.
IS [ NOT ] NULL Page Compares an expression or value and determines whether it is NULL.
Joins Page Joins combine data from multiple, related data sources is a single result set. Join expressions appear in the WHERE clause.
LAST_DAY Page LAST_DAY returns the last day of the month.
LCASE / LOWER Page Converts a string value to lowercase characters.
LEFT Page Returns the leftmost characters in a string value, based on a specified number of characters.
Less Than Page Compares two expressions, and returns true if the first value is less than the second value.
Less Than or Equal To Page Compares two expressions, and returns true if the first value is less than or equal to the second value.
LIKE Page Compares a character value to a specified character pattern.
LN / LOG Page Returns the natural logarithm of a number. LN and LOG are both supported, perform the same function, and can be used interchangeably
LOCATE Page If a specified substring is found within a specified search string, LOCATE returns the index at which the substring is found within the search string. If the...
LOG10 Page Returns the base-10 logarithm of a number.
LONG VARCHAR Data Type Page LONG VARCHAR is a Variable Length string type data type.
LONG VARCHAR FOR BIT DATA Data Type Page LONG VARCHAR FOR BIT DATA is a variable length Binary Data Type.
LTRIM Page Trims spaces from the start of a string value.
Manage Data Projects Page View, create, edit and delete data copy projects.
MASK_CC() Page MASK_CC masks all but the last 4 digits of a standard 16 digit credit card number.
MASK_EMAIL1 () Page The email address is fully masked with the exception of the first and last character.
MASK_EMAIL2 () Page Mask the entire address except the suffix.
MASK_EMAIL3 () Page MASK_EMAIL3 masks the entire email except the domain and suffix.
MASK_FIRST () Page Mask specified number of column characters beginning at left. if no value specified the default is 3.
MASK_FULL() Page Mask an entire data type with a matching mask in any SELECT query.
MASK_HASH () Page Use a hashing function to change the column value. If no type specified, MASK_HASH uses SHA-256.
MASK_LAST () Page Mask specified number of column characters beginning at right.
MASK_NUMERIC () Page MASK_NUMERIC is similar to MASK_HASH except output is masked as numbers. You can specify length and use hashing algorithms like MD5, SHA‌-1, SHA‌-256.
MASK_OUT () Page Mask out masks 60% of characters in any selected column.
MASK_PHONE () Page Mask the last 5 characters of any phone number.
MASK_RANDOM () Page MASK_RANDOM returns a random value from that has the same length as the original value.
MASK_SSN() Page MASK_SSN masks the last 4 characters of the social security number value, and does not accept any other parameter.
MASK_SUB () Page Use MASK_SUB to mask the specified length of characters starting from the startindex.
MAX Page MAX returns the maximum value in a data set.
MIN Page Min returns the minimum value in a data set.
MINUTE Page MINUTE returns the minute portion of a value.
MOD Page Returns the remainder of the division of two integer values.
MONTH Page MONTH returns the month portion of a value.
Multiplication Page Multiplies two numeric values.
NATIVE Page NATIVE is a utility function used with SELECT or WHERE clauses. NATIVE executes any command in single quotes exactly as written on the target database.
Not Equal Page Compares two expressions for equivalence, and returns a true value if they are found to be unequal.
Null data_types Page All supported data_types can be NULL. Although NULL is not a value, but the absence of a value, it is sometimes referred to as a “NULL value”.
NULLIF Page NULLIF returns NULL when the two arguments are equivalent.
Numeric data_types Page This section discusses the Cirro-provided numeric data_types.
PI Page Returns the value of PI.
POWER Page Raises one number to the power of a second number.
Privilege Mappings Page Cirro has built-in privileges which can be granted, revoked, or denied.
Privilege Statement Execution and Precedence Page Cirro privileges are executed in a specific order.
QUARTER Page QUARTER returns the quart of the year in which the day is found, as an integer from 1 to 4, and where the first quarter of the year is “1”.
Query Operators Page The following commands can be used to combine multiple queries.
RADIANS Page Performs an approximate conversion of a number from degrees to radians.
RAND Page Returns a random number given a seed number.
RANDOM Page Returns a random number.
REAL Data Type Page REAL is a Floating Point numeric type data type.
REFRESH MATERIALIZED VIEW Page Refreshes the data in a materialized view.
REPLACE Page Replaces one or more occurrences of a specified string value with another specified value.
RESTRICT USER Page Sets user account in state where user must specify a new password on next login. No other actions are allowed until the password is changed.
REVOKE privilege Page Removes a previously granted or denied privilege.
REVOKE SELECT on encryption key Page REVOKE SELECT on an encryption key from users or roles.
REVOKE role Page Removes a role from a Cirro user or role. It must be performed prior to a DROP .
RIGHT Page Replaces one or more occurrences of a specified string value with another specified value.
ROWCHECKSUM function Page ROWCHECKSUM returns a N-bit checksum that returns the same result for the same data on different RDBMS'. It has the same syntax and arguments as CHECKSUM.
RTRIM Page Trims spaces from the end of a string value.
SECOND Page SECOND returns the seconds portion of a value.
SELECT Page Retrieves data from a structured data source. The SELECT statement can be used individually, or with one or more of clauses or joins.
SET CACHEPOLICY Page Sets a cache policy for a specified federated system. This is a global setting, and will affect all users and all sessions for this system.
SET DEFAULT AUTHENTICATION PROVIDER Page Optionally set a default provider to enable authentication for users not explicitly managed by Cirro. Typically this will be an Active Directory or LDAP prov...
SET DESCRIBE Page Enables return of query metadata, including column names and data_types.
SET NUMBEROFRDBMSTRANSFERTHREADS Page Specifies the session-specific number of RDBMS transfer threads available to the query optimizer.
SET PASSTHROUGH Page Execute SQL commands directly on any system your cirro account has been granted permissions.
SET SQUOOP OPTIONS Page Allows user to specify Sqoop options to override the Cirro default values. These values will be used in Sqoop for data movement between all relational databa...
SHOW PROJECT Page Display any data project using these commands.
SIGN Page Returns the sign of a numeric value.
SIN Page Returns the sine of a numeric value.
SMALLINT Data Type Page SMALLINT is a numeric data type.
SMALLINT Page Use this function to convert a value to a SMALLINT.
SQL Syntax notation Page Find out about SQL Syntax notation.
SQRT Page Returns the square root of a numeric value.
SUBSTR Page Returns a substring of a source string, based on starting position and substring length.
Subtraction Arithmetic Operator Page Subtracts two numeric values.
SUM Page SUM returns the aggregate sum of values in a data set..
Cirro Privileges - Supported Securables Page These statements control which privileges are available to which users and roles.
SYNC - match rows on target key columns Page Two SYNC statements to sync data between source and target.
SYNC Target TO Source Page This syntax automatically triggers a COMPARE in order to make the target table, cursor, rows and key columns match the source.
SYNC Target WITH LOG table Page This command performs the actions on the target table that have been saved in the log table.
SYNC with KEY Page Where a Key value exists in several rows, a second key must be used to avoid accidental deletions.
SYSCS_UTIL.SYSCS_FREE_CACHE() Page Empties the system cache.
SYSTEM_USER Page Returns the username of the user which was used to run the query on the data source.
TAN Page
TIME Data Type Page The TIME Data Type Stores time values without date components.
TIME Page TIME returns the time portion of a value.
TIMESTAMP Data Type Page The TIMESTAMP Data Type stores a date and time.
TIMESTAMP Page TIMESTAMP returns the timestamp portion of a value.
TIMESTAMPDIFF Date Time Function Page TIMESTAMPDIFF returns the difference between two timestamp values, based on a specified interval type.
TRIM Page Trims spaces from the start and end of a string value.
UCASE / UPPER Page Converts a string value to uppercase characters.
VALIDATE AUTHENTICATION Page Use VALIDATE AUTHENTICATION to check that you can log in to Cirro using a specified authentication provider.
VALIDATE AUTHENTICATION Page Use VALIDATE AUTHORIZATION to give authorization to the given datasource using the supplied username and password.
VARCHAR Data Type Page VARCHAR is a Variable Length string data type.
VARCHAR FOR BIT DATA Data Type Page VARCHAR FOR BIT DATA is a variable length Binary Data Type.
View CONNECTION RULE tables Page View the connection rules or the execution log.
View User tables Page View system tables relevant to USER Accounts
WEEK Page WEEK returns the week of the year in which the day is found, as an integer from 1 to 52, and where the first week of the year is 1.
YEAR Page YEAR returns the year portion of a value.