Unified SQL syntax

In some CCH Tagetik definitions, where SQL language can be entered manually, you can use a unified custom syntax for certain functions (see table below), and this will then be translated into the language of the current vendor runtime DB. This functionality allows you to create “independent vendor DB” setups, which can be exported and imported on other application environments installed on other vendor DB without having to then convert the SQL syntax manually.

The following tools allow this syntax to be used:

• ETL (Extraction Transformation and Loading),
• DTP (Data Transformation Package),
• QDL (Quick Data Loader)
• Query-type application objects used in the QDL

Square brackets indicate that the function’s topic is optional.

Syntax	SQLServer	Oracle	Postgres	Hana
F_SUBSTR(FIELD, 1, 3)	SUBSTRING(FIELD, 1, 3)	SUBSTR(FIELD, 1, 3)	SUBSTRING(FIELD, 1, 3)	SUBSTRING(FIELD, 1, 3)
F_LENGTH(FIELD)	LEN(FIELD)	LENGTH(FIELD)	LENGTH(FIELD)	LENGTH(FIELD)
F_INSTR(FIELD, WHAT)	CHARINDEX(WHAT, FIELD)	INSTR(FIELD, WHAT)	POSITION(WHAT IN FIELD)	INSTR(FIELD, WHAT)
F_CONCAT	+
F_LPAD(FIELD, N, EXPR)	RIGHT(REPLICATE(EXPR, N) + FIELD, N)	LPAD(FIELD, N, EXPR)	LPAD(FIELD, N, EXPR)	LPAD(FIELD, N, EXPR)
F_TO_CHAR(FIELD, [format])	CONVERT(VARCHAR, FIELD, [format_code])	TO_CHAR(FIELD,[format])	TO_CHAR(FIELD, format)	TO_CHAR(FIELD,[format])
F_TO_DATE(FIELD, [format])	CONVERT(DATETIME, FIELD,[format_code])	TO_DATE(FIELD,[format])	TO_DATE(FIELD, format)	TO_DATE(FIELD, [format])
F_TO_NUMBER(FIELD, [CifreTotali [, CifreDecimali]])	CONVERT(NUMERIC[(CifreTotali[,CifreDecimali])], FIELD)	TO_NUMBER(FIELD)	TO_NUMBER(FIELD, format)	TO_NUMBER(FIELD)
F_TO_TIMESTAMP(FIELD, [format])	CONVER (DATETIME, FIELD, [format])	TO_TIMESTAMP(FIELD, [format])	TO_TIMESTAMP(FIELD, [format])	TO_TIMESTAMP(FIELD, [format])
F_DATE_TO_TIMESTAMP(FIELD)	CONVERT(DATETIME, FIELD)	TO_TIMESTAMP(FIELD)	CAST (FIELD AS TIMESTAMP)	TO_TIMESTAMP(FIELD)
F_NVL(FIELD, VALUE)	ISNULL(FIELD, VALUE)	NVL(FIELD, VALUE)	COALESCE(FIELD, VALUE)	IFNULL(FIELD, VALUE)
F_NEWDATE	GETDATE()	SYSDATE	current_timestamp	current_timestamp
F_EXCELDATE(FIELD, [format])	CONVERT(VARCHAR(10), DATEADD(d, CAST(FIELD AS FLOAT),'1899-12-30'), [format])	TO_CHAR(TO_DATE('1899-12-30', 'YYYY-MM-DD') + FIELD,[format])	TO_CHAR(TO_DATE('1899-12-30', 'YYYY-MM-DD') + FIELD::int, format)	TO_CHAR(ADD_DAYS(TO_DATE ('1899-12-30', 'YYYY-MM-DD'), FIELD), 'YYYY-MM-DD')
F_DATEADD(day, N, FIELD)	DATEADD(day, N, FIELD)	FIELD + INTERVAL 'N' day	FIELD + INTERVAL 'N day'	ADD_DAYS(FIELD, N)
F_DATEADD(month, N, FIELD)	DATEADD(month, N, FIELD)	FIELD + INTERVAL 'N' month	FIELD + INTERVAL 'N month'	ADD_MONTHS(FIELD, N)
F_DATEADD(year, N, FIELD)	DATEADD(year, N, FIELD)	FIELD + INTERVAL 'N' year	FIELD + INTERVAL 'N year'	ADD_YEARS(FIELD, N)
F_YEAR(FIELD)	YEAR(FIELD)	EXTRACT(year FROM FIELD)	EXTRACT(year FROM FIELD)	YEAR(FIELD)
F_MONTH(FIELD)	MONTH(FIELD)	EXTRACT(month FROM FIELD)	EXTRACT(month FROM FIELD)	MONTH(FIELD)
F_DAY(FIELD)	DAY(FIELD)	EXTRACT(day FROM FIELD)	EXTRACT(day FROM FIELD)	DAYOFMONTH(FIELD)
F_EOMONTH(theDateTime)	EOMONTH(theDateTime)	TRUNC(LAST_DAY(theDateTime))	(date_trunc('MONTH', theDateTime) + INTERVAL '1 MONTH - 1 day')::date)	LAST_DAY(theDateTime)
F_EOMONTH(theDatetime, Offset)	EOMONTH(theDatetime, Offset)	TRUNC(LAST_DAY(ADD_MONTHS(theDatetime, theOffset)))	((date_trunc('MONTH', theDatetime + INTERVAL 'theOffset MONTH') + INTERVAL '1 MONTH - 1 day')::date)	LAST_DAY(ADD_MONTHS(theDateTime, theOffset))
F_DATEDIFF(DAY, firstdate,seconddate)	DATEDIFF(DAY, firstdate, seconddate)	(TO_DATE(TO_CHAR(seconddate,'DD-MON-YYYY')) - TO_DATE(TO_CHAR(firstdate,'DD-MON-YYYY')))	(seconddate::date - firstdate::date)	DAYS_BETWEEN(TO_DATE(firstdate), TO_DATE(seconddate))
F_DATEDIFF(MONTH, firstdate,seconddate)	DATEDIFF(MONTH, firstdate, seconddate)	((EXTRACT(YEAR from seconddate) - EXTRACT(YEAR from firstdate)) * 12 + (EXTRACT(MONTH from seconddate) - EXTRACT(MONTH from firstdate)))	((DATE_PART('year', seconddate) - DATE_PART('year', firstdate)) * 12 + (DATE_PART('month', seconddate) - DATE_PART('month', firstdate)))	((YEAR(seconddate) - YEAR(firstdate)) * 12 + (MONTH(seconddate) - MONTH(firstdate)))
F_DATEDIFF(YEAR, firstdate,seconddate)	DATEDIFF(YEAR, firstdate, seconddate)	(EXTRACT(YEAR from seconddate) - EXTRACT(YEAR from firstdate))	(DATE_PART('year', seconddate) - DATE_PART('year', firstdate))	(YEAR(seconddate) - YEAR(firstdate))
F_STUFF(inputString, startIndex, length, replaceWithString)	STUFF (character_expression , start , length , replaceWith_expression)	substr(pExpr, 1, pStart - 1)		pReplace
F_dbo.	dbo.
F_STRING_AGG(expression, separator)	STRING_AGG(expression, separator)	LISTAGG(expression, separator)	STRING_AGG(expression, separator)	STRING_AGG(expression, separator)
F_CEILING(numeric)	CEILING(numeric)	CEIL(numeric)	CEILING(numeric)	CEIL(numeric)
F_CHAR(numeric)	CHAR(numeric)	CHR(numeric)	CHR(numeric)	CHAR(numeric)
F_ISNUMERIC(expression)	ISNUMERIC(expression)	CASE WHEN regexp_like(%1$s, '^(-)?\d+(.\d+)?(\,\d+)?$') THEN 1 ELSE 0 END	CASE WHEN %1$s ~ '^(-)?\d+(.\d+)?(\,\d+)?$' is true THEN 1 ELSE 0 END	locate_regexpr(START '^(-)?\d+(.\d+)?(\,\d+)?$' IN %1$s)
F_EQUAL(expr1, expr2)	CAST(expr1 AS VARBINARY(MAX)) = CAST(expr2 AS VARBINARY(MAX))	expr1 = expr2	expr1 = expr2	expr1 = expr2
F_DOUBLEQUOTE([Campo1])	''Campo1''	''Campo1''	''Campo1''	''Campo1''
F_MD5(String)	HASHBYTES('MD5', String)	STANDARD_HASH(String, 'MD5')	('\x'
F_MOD(num1,num2)	num1 % num2	MOD(num1,num2)	MOD(num1,num2)	MOD(num1,num2)
F_EXT_DOUBLEQUOTE	''FIELD''	''FIELD''	''FIELD''	''FIELD''
F_PREVIEW	JSON_VALUE(FIELD, '$.preview')	JSON_VALUE(FIELD, '$.preview')	JSON_EXTRACT_PATH_TEXT(FIELD::json, 'preview')	JSON_VALUE(FIELD, '$.preview')

To use unified sql syntax, such as an EXECUTE like:

EXECUTE 'select cod_periodo,F_NVL(note, ''A'') from dati_saldi_ic where cod_azienda=''A3'' '

it is necessary to replace the superscripts with the F_DOUBLEQUOTE as follows:

EXECUTE 'select cod_periodo,F_NVL(note, F_DOUBLEQUOTE(A)) from dati_saldi_ic where cod_azienda=''A3'' '

Note: the F_DOUBLEQUOTE must be used when, in one of the functions listed above, it is necessary to enclose a parameter between two single quotes (F_NVL(notes, ''A'')).

Attention: When writing queries, the following system tables cannot be used for the Postgres DB:

• pg_role
• pg_user​
• pg_group​
• pg_database​
• pg_stat_activity​
• pg_stat_database​
• pg_stat_database_conflicts​
• information_schema

Examples:

• F_TO_CHAR

SELECT * FROM DATI_SALDI_LORDI WHERE F_TO_CHAR(DATEUPD,'DD/MM/YYYY') = '01/09/2017'

Translations:

SQLServer: CONVERT(VARCHAR,DATEUPD,103)

ORACLE: TO_CHAR(DATEUPD,'DD/MM/YYYY')

POSTGRES: TO_CHAR(DATEUPD,'DD/MM/YYYY')

HANA: TO_CHAR(DATEUPD,'DD/MM/YYYY')

SELECT * FROM DATI_SALDI_LORDI WHERE F_TO_CHAR(DATEUPD) = '01/09/2017'

Translations:

SQLServer: CONVERT(VARCHAR,DATEUPD)

ORACLE: TO_CHAR(DATEUPD)

POSTGRES: error (as reported in the table, the two topics are mandatory)

HANA: TO_CHAR(DATEUPD)

SELECT F_TO_CHAR(IMPORTO, '999999.999') FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(VARCHAR, IMPORTO)

ORACLE: TO_CHAR(IMPORTO, '999999.999')

POSTGRES: TO_CHAR(IMPORTO, '999999.999')

HANA: TO_CHAR(IMPORTO, '999999.999')

• F_TO_DATE

SELECT F_TO_DATE('01/09/2017','DD/MM/YYYY') FROM DATI_SALDI_LORDI

Note: the separator character of the first and second parameter must coincide.

Translations:

SQLServer: CONVERT(DATETIME,'01/09/2017,103)

ORACLE: TO_DATE('01/09/2017,'DD/MM/YYYY')

POSTGRES: TO_DATE('01/09/2017,'DD/MM/YYYY')

HANA: TO_DATE('01/09/2017,'DD/MM/YYYY')

SELECT F_TO_DATE('01/09/2017') FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(DATETIME,'01/09/2017')

ORACLE: TO_DATE('01/09/2017')

POSTGRES: error (as reported in the table, the two topics are mandatory)

HANA: TO_DATE('01/09/2017')

• F_TO_NUMBER

SELECT *, F_TO_NUMBER( '11998.261', 7, 2) FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(NUMERIC(7, 2),'11998.261')

ORACLE: TO_NUMBER('11998.261') non tiene conto dei parametri cifre totali e cifre decimali

POSTGRES: TO_NUMBER('11998.261', '99999.99')

HANA: TO_NUMBER('11998.261') does not take account of the total figure or decimal digit parameters

Caution: an error will be returned if the total number of figures specified is less than the number of decimal figures (e.g. F_TO_NUMBER( '11998.261', 2, 7)), with the exception of Oracle and Hana, which do not take account of these two parameters.

SELECT *, F_TO_NUMBER( '11998.261', 5) FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(NUMERIC(5),'11998.261')

ORACLE: TO_NUMBER('11998.261') non tiene conto dei parametri cifre totali e cifre decimali

POSTGRES: TO_NUMBER('11998.261', '99999.')

HANA: TO_NUMBER('11998.261') does not take account of the total figure or decimal digit parameters

Caution: an error will be returned if the number of figures comprising entire part of the first parameter is greater than the specified number of total figures (e.g. F_TO_NUMBER( '11998.261', 3)), with the exception of Oracle and Hana.

SELECT *, F_TO_NUMBER( '11998.261') FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(NUMERIC,'11998.261')

ORACLE: TO_NUMBER('11998.261')

POSTGRES: error, at least the number of total figures must be specified.

HANA: TO_NUMBER('11998.261')

• F_EXT_DOUBLEQUOTE

SELECT F_EXT_DOUBLEQUOTE(F_TO_DATE('01/01/2024', 'DD/MM/YYYY')) FROM DATI_SALDI_LORDI

Translations:

SQLServer: CONVERT(DATETIME,''01/01/2024'',103)

ORACLE:TO_DATE(''01/01/2024'',''DD/MM/YYYY'')

POSTGRES: TO_DATE(''01/01/2024'',''DD/MM/YYYY'')

HANA: TO_DATE(''01/01/2024'',''DD/MM/YYYY'')

Attention: F_EXT_DOUBLEQUOTE applies to strings or other single functions. If you have several disjointed functions, it must be repeated externally for each block.

F_EXT_DOUBLEQUOTE(FIELD)

Attention: the F_EXT_DOUBLEQUOTE only affects single quotes. If an argument without quotes or with double quotes is passed to it, it does not perform any transformation.

• F_EXT_DOUBLEQUOTE(FIELD) -> restituisce FIELD

• F_EXT_DOUBLEQUOTE('FIELD') -> restituisce ''FIELD''

• F_EXT_DOUBLEQUOTE(''FIELD'') -> restituisce ''FIELD''

• F_PREVIEW

Note: the FIELD field must contain a json string structured as follows: `{

"key": {

    "storage": "storage type",

    "id": "rich text oid",

    "reference": "[db]\\\\[aw]\\\\[dataset]",

    "version": 1

},

"preview": "preview text"

}`