Built-in Connector-specific Functions

In addition to the standard scalar functions supported by CData Virtuality, there are specific predefined functions unique to certain connectors based on their underlying DBMS functions. Detailed information about these functions can be found in the Functions section for each data source within CData Virtuality Studio. These functions are pushed down for exclusive use within their respective data sources.

To enable these predefined functions for a data source, set the importer property importer.loadSourceSystemFunctions to TRUE.

Currently, predefined functions are fully supported for the following DBMSs:

Microsoft SQL Server: CData Virtuality supports functions from Microsoft SQL Server v14, v15 and v16. While lower versions can be used, they are not fully supported;
Microsoft Azure Database: v12 functions are supported;
Snowflake: v8 functions are supported.

Snowflake functions are supported since v4.8

Information about built-in connector-specific functions could be found in SYS.Functions and SYS.FunctionParams tables.

Information about built-in connector-specific functions is located in SYS.Functions and SYS.FunctionParams tables since v4.8

Example

SQL

SELECT mssql.CHOOSE(a, 'First', 'Second', 'Third'), a FROM mssql.test_a;;

Aggregate Functions Usage

Aggregate functions should be used with GROUP BY with a single quoted string as an aggregate expression.

Example

CODE

SELECT mssql.MAX('a') FROM mssql.test_agg GROUP BY b ORDER BY b desc;;

In this example, a used as a parameter in the MAX function is a column name.

IIF, ISNULL, TRY_PARSE Usage

IIF, ISNULL, TRY_PARSE should be used with single quoted string expressions. String constants should be enclosed in triple single quotes.

Examples

CODE

SELECT mssql.IIF('45 > 60', 'a', 'b');;
SELECT mssql.IIF('a > b', a, b) FROM mssql.test_a WHERE b=10;;
 
SELECT mssql.ISNULL('NULL', 'replacement');;
SELECT mssql.ISNULL('''test''', 'replacement');;
SELECT mssql.ISNULL(mssql.ERROR_MESSAGE(), 'replacement');;
 
SELECT mssql.TRY_PARSE('''Monday, 13 December 2010''', 'AS datetime2 USING ''en-US''');;
SELECT mssql.TRY_PARSE('CHAR_1_COL', 'AS real') FROM mssql.test_cast_funcs;;

Asterisk and DISTINCT Usage

COUNT, COUNT_BIG, BINARY_CHECKSUM, CHECKSUM should be used with a single quoted asterisk.

Some functions can be used with DISTINCT in parameters enclosed with single quotes.

Examples

CODE

SELECT mssql.COUNT('*') FROM mssql.test_agg;;
SELECT mssql.VARP('DISTINCT a') FROM mssql.test_agg;;

Window Expression as a Parameter

Some functions (e.g. analytical functions) use window expression as the last parameter.

Examples

CODE

SELECT mssql.LAG(a, 1, 0, 'OVER(ORDER BY b)') FROM mssql.test_a;;
SELECT mssql.LEAD(a, 1, 0, 'OVER(ORDER BY b)') FROM mssql.test_a;;
SELECT mssql.FIRST_VALUE(b, 'OVER (ORDER BY a)') FROM mssql.test_a;;
SELECT mssql.ROW_NUMBER('OVER(ORDER BY a)'), a FROM mssql.test_a;;

Restrictions

Usage Within Same Data Source Only

Built-in functions of the data source can be used exclusively with tables and columns from the same data source.

Valid usage example:

SQL

SELECT mssql.CHOOSE(a, 'First', 'Second', 'Third'), a FROM mssql.test_a;;

Invalid usage examples:

CODE

SELECT mssql.CHOOSE(a, 'First', 'Second', 'Third'), a FROM mssql_other_db.test_a;;
SELECT mssql.CHOOSE(a, 'First', 'Second', 'Third'), a FROM postgres.test_a;;

Materialized Tables

Built-in functions cannot be used with materialized tables because these tables are physically located in a different data source. Instead, you can rewrite the query to use a similar function from the Analytical Storage if the Analytical Storage data source also supports it.

For example, if mssql.test_a table is materialized to analytical_storage and the corresponding recommended optimization is enabled:

Valid usage example (if both Analytical Storage and the source database are Microsoft SQL Server):

SQL

SELECT analytical_storage.CHOOSE(a, 'First', 'Second', 'Third'), a FROM mssql.test_a;;

Invalid usage example:

SQL

SELECT mssql.CHOOSE(a, 'First', 'Second', 'Third'), a FROM mssql.test_a;;

Combining Built-in Functions with CData Virtuality Functions

Combining connector-specific built-in functions with the CData Virtuality functions that cannot be pushed down is not allowed. For example, the following queries will produce errors:

CODE

SELECT URLENCODE(mssql.Fun_Concat('http//', a), 'UTF-8') FROM mssql.test_a;;
SELECT mssql.Fun_Concat(URLDECODE('http//' + a, 'UTF-8'), a) FROM mssql.test_a;;

Developer Preview Functions

The following Microsoft SQL Server functions are currently in developer preview:

APPLOCK_MODE, APPLOCK_TEST, APPROX_PERCENTILE_CONT, APPROX_PERCENTILE_DISC, ASSEMBLYPROPERTY, ASYMKEY_ID, ASYMKEYPROPERTY, CERT_ID, CERTENCODED, CERTPROPERTY, DECRYPTBYASYMKEY, DECRYPTBYCERT, DECRYPTBYKEY, DECRYPTBYKEYAUTOASYMKEY, DECRYPTBYKEYAUTOCERT, DECRYPTBYPASSPHRASE, EDGE_ID_FROM_PARTS, ENCRYPTBYASYMKEY, ENCRYPTBYCERT, ENCRYPTBYKEY, ENCRYPTBYPASSPHRASE, ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, EVENTDATA, GRAPH_ID_FROM_EDGE_ID, GRAPH_ID_FROM_NODE_ID, GROUPING, GROUPING_ID, INDEX_COL, INDEXKEY_PROPERTY, INDEXPROPERTY, IS_OBJECTSIGNED, KEY_GUID, KEY_ID, KEY_NAME, NODE_ID_FROM_PARTS, OBJECT_ID_FROM_EDGE_ID, OBJECT_ID_FROM_NODE_ID, PERCENTILE_CONT, PERCENTILE_DISC, SCOPE_IDENTITY, SIGNBYASYMKEY, SIGNBYCERT, SQL_VARIANT_PROPERTY, SYMKEYPROPERTY, TRIGGER_NESTLEVEL, VERIFYSIGNEDBYASYMKEY, VERIFYSIGNEDBYCERT

Database Built-in Functions support is available since v4.6

Database Built-in Functions support is extended for Microsoft SQL Server with around 250 functions including aggregate functions, window functions, IFNULL, ISNULL, TRY_PARSE, CHECKSUM, etc, and are available also for v14 and v15 since v4.7