统一 SQL 语法

在某些 CCH Tagetik 定义中，可以手动输入 SQL 语言，此时你可以对特定函数使用统一的自定义语法（见下表），随后该语法会被转换为当前供应商运行时数据库的语言。此功能允许你创建"独立于供应商数据库"的配置，这些配置可以导出并导入到安装在其他供应商数据库上的其他应用环境中，而无需手动转换 SQL 语法。

以下工具允许使用此语法：

ETL（抽取、转换和加载），
DTP（数据转换包），
QDL（快速数据加载器）
QDL 中使用的查询类型应用对象

方括号表示该函数的主题为可选。

语法	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')

要使用统一 SQL 语法，例如像这样的 EXECUTE：

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

需要将上标替换为 F_DOUBLEQUOTE，如下所示：

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

注意：当上述某个函数中需要将参数用两个单引号括起来时（F_NVL(notes, ''A'')），必须使用 F_DOUBLEQUOTE。

注意：编写查询时，Postgres 数据库不能使用以下系统表：

pg_role
pg_user
pg_group
pg_database
pg_stat_activity
pg_stat_database
pg_stat_database_conflicts
information_schema

示例：¶

F_TO_CHAR¶

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

转换结果：

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'

转换：

SQLServer: CONVERT(VARCHAR,DATEUPD)

ORACLE: TO_CHAR(DATEUPD)

POSTGRES: 错误（如表中所列，这两个主题为必填项）

HANA: TO_CHAR(DATEUPD)

SELECT F_TO_CHAR(IMPORTO, '999999.999') FROM DATI_SALDI_LORDI

转换：

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

注意：第一个和第二个参数的分隔符必须一致。

转换：

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

转换：

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

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

POSTGRES: 错误（如表中所列，这两个主题为必填项）

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

F_TO_NUMBER¶

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

转换：

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

ORACLE: TO_NUMBER('11998.261') 不考虑总位数和小数位数参数

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

HANA: TO_NUMBER('11998.261') 不考虑总位数或小数位数参数

注意：如果指定的总位数小于小数位数（例如 F_TO_NUMBER( '11998.261', 2, 7)），将返回错误，但 Oracle 和 Hana 除外，它们不考虑这两个参数。

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

转换：

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

ORACLE: TO_NUMBER('11998.261') 不考虑总位数和小数位数参数

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

HANA: TO_NUMBER('11998.261') 不考虑总位数或小数位数参数

注意：如果第一个参数整数部分的位数大于指定的总位数（例如 F_TO_NUMBER( '11998.261', 3)），将返回错误，但 Oracle 和 Hana 除外。

SELECT *, F_TO_NUMBER( '11998.261') FROM DATI_SALDI_LORDI

转换：

SQLServer: CONVERT(NUMERIC,'11998.261')

ORACLE: TO_NUMBER('11998.261')

POSTGRES: 错误，至少必须指定总位数。

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

转换：

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

注意：F_EXT_DOUBLEQUOTE 适用于字符串或其他单个函数。如果有多个互不相连的函数，必须为每个块在外部重复调用。

F_EXT_DOUBLEQUOTE(FIELD)

注意：F_EXT_DOUBLEQUOTE 仅影响单引号。如果传入的参数没有引号或使用双引号，则不执行任何转换。

F_EXT_DOUBLEQUOTE(FIELD) -> 返回 FIELD
F_EXT_DOUBLEQUOTE('FIELD') -> 返回 ''FIELD''
F_EXT_DOUBLEQUOTE(''FIELD'') -> 返回 ''FIELD''
F_PREVIEW¶

注意：FIELD 字段必须包含如下结构的 json 字符串： `{

"key": {

"storage": "storage type",

"id": "rich text oid",

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

"version": 1

},

"preview": "preview text"

}`

统一 SQL 语法

示例：¶

F_TO_CHAR¶

F_TO_DATE¶

F_TO_NUMBER¶

F_EXT_DOUBLEQUOTE¶

F_PREVIEW¶