sqltranslate ()
描述：
将标准SQL中的函数翻译成指定数据库中的格式。
语法：
sql.sqltranslate(dbtype)
备注：
将标准SQL中的函数翻译成指定数据库中的格式。默认支持的数据库类型有：ORACLE ,SQLSVR ,DB2 ,MYSQL ,HSQL ,TERADATA ,POSTGRES，除此之外用户还可以通过修改配置文件自定义数据库类型及函数。
标准函数对照：
字符串函数
|
标准函数 |
含义 |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
LOWER(str) |
转成小写 |
|
|
|
|
|
|
|
|
UPPER(str) |
转成大写 |
|
|
|
|
|
|
|
|
LTRIM(str) |
删除前导空白 |
|
|
|
|
|
|
|
|
RTRIM(str) |
删除后缀空白 |
|
|
|
|
|
|
|
|
TRIM(str) |
删除两端空白 |
TRIM(str) |
LTRIM(RTRIM(str)) |
LTRIM(RTRIM(str)) |
TRIM(str) |
TRIM(str) |
|
LTRIM(RTRIM(str)) |
|
SUBSTR(str,start,len)
SUBSTR(str,start) |
子串 |
SUBSTR(str,start,len)
SUBSTR(str,start) |
SUBSTRING(str,start,len)
SUBSTRING(str,start,len(str)-start+1) |
SUBSTR(str,start,len)
SUBSTR(str,start) |
SUBSTR(str,start,len)
SUBSTR(str,start) |
SUBSTR(str,start,len)
SUBSTR(str,start) |
SUBSTR(str,start,len)
SUBSTR(str,start) |
SUBSTR(str,start,len)
SUBSTR(str,start) |
|
LEN(str) |
串长 |
LENGTH(str) |
LEN(str) |
LENGTH(str) |
LENGTH(str) |
CHAR_LENGTH(str) |
CHAR_LENGTH(str) |
CHAR_LENGTH(str) |
|
INDEXOF(sub,str[,start]) |
子串的位置 |
INSTR(str,sub[,start]) |
CHARINDEX(sub,str[,start]) |
LOCATE(sub,str[,start]) |
LOCATE(sub,str[,start]) |
INDEX(str,sub)
3个参数时：n/a |
LOCATE(sub,str[,start]) |
INDEX(str,sub)
3个参数时：n/a |
|
LEFT(str,len) |
str中最左边len个字符 |
SUBSTR(str,1,len) |
LEFT(str,len) |
LEFT(str,len) |
LEFT(str,len) |
SUBSTR(str,1,len) |
LEFT(str,len) |
SUBSTR(str,1,len) |
|
RIGHT(str,len) |
str中最右边len个字符 |
CASE WHEN len=0 THEN '' WHEN LENGTH(str)>len THEN SUBSTR(str,-len) ELSE str END |
RIGHT(str,len) |
RIGHT(str,len) |
RIGHT(str,len) |
CASE WHEN len=0 then '' WHEN CHAR_LENGTH(str)>len THEN SUBSTR(str,CHAR_LENGTH(str)-len,len) ELSE str END |
RIGHT(str,len) |
CASE WHEN len=0 then '' WHEN CHAR_LENGTH(str)>len THEN SUBSTR(str,CHAR_LENGTH(str)-len,len) ELSE str END |
|
CONCAT(str1,str2) |
连接2个字符串 |
CONCAT(str1,str2) |
str1+str2 |
CONCAT(str1,str2) |
CONCAT(str1,str2) |
str1||str2 |
str1||str2 |
str1||str2 |
|
REPLACE(str,sub,rplc) |
将str中的sub替换成rplc |
|
|
|
|
OREPLACE(str,sub,rplc) |
|
|
数值函数
|
标准函数 |
含义 |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
ABS(x) |
求绝对值 |
|
|
|
|
|
|
|
|
ACOS(x) |
求反余弦 |
|
|
|
|
|
|
|
|
ASIN(x) |
求反正弦 |
|
|
|
|
|
|
|
|
ATAN(x) |
求反正切 |
|
|
|
|
|
|
|
|
ATAN2(x,y) |
求反正切 |
ATAN2(x,y) |
ATN2(x,y) |
ATAN2(x,y) |
ATAN2(x,y) |
|
|
|
|
CEIL(x) |
求大于等于x的最小整数 |
CEIL(x) |
CEILING(x) |
CEIL(x)/CEILING(x) |
CEIL(x)/CEILING(x) |
CEIL(x) |
|
CEIL(x) |
|
COS(x) |
求余弦 |
|
|
|
|
|
|
|
|
EXP(x) |
求e的x次幂 |
|
|
|
|
|
|
|
|
FLOOR(x) |
求小于等于x的最大整数 |
|
|
|
|
|
|
|
|
LN(x) |
求自然对数 |
LN(x) |
LOG(x) |
LN(x)/LOG(x) |
LN(x)/LOG(x) |
LN(x) |
|
|
|
LOG10(x) |
求以10为底的对数 |
LOG(10,x) |
LOG10(x) |
LOG10(x) |
LOG10(x) |
LOG(x) |
LOG10(x) |
LOG(x) |
|
MOD(x,m) |
x模m |
MOD(x,m) |
x%m |
MOD(x,m) |
x%m |
x MOD m |
|
x%m |
|
POWER(x,y) |
x的y次幂 |
|
|
|
|
|
|
|
|
ROUND(x,n) |
四舍五入 |
|
|
|
|
|
|
|
|
SIGN(x) |
求符号 |
|
|
|
|
|
|
|
|
SIN(x) |
求正弦 |
|
|
|
|
|
|
|
|
SQRT(x) |
平方根 |
|
|
|
|
|
|
|
|
TAN(x) |
正切 |
|
|
|
|
|
|
|
|
TRUNC(x,n) |
截断 |
TRUNC(x,n) |
CONVERT(BIGINT,x*POWER(10,n))/POWER(10.0,n) |
TRUNC(x,n) |
TRUNCATE(x,n) |
TRUNC(x,n) |
|
|
|
RAND(seed) |
随机数 |
DBMS_RANDOM.VALUE(0, seed)
//返回0-seed之间的数 |
RAND(seed)//返回0-1之间的数值 |
RAND(seed)//返回0-1之间的数 |
RAND(seed) |
RANDOM(0,seed)
//返回0到seed间的int |
RAND(seed)
//返回0-1之间的数 |
RANDOM() |
时间函数
|
标准函数 |
含义 |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
YEAR(d) |
取年 |
EXTRACT(YEAR FROM d) |
YEAR(d) |
YEAR(d) |
YEAR(d) |
EXTRACT(YEAR FROM d)/TD_YEAR_OF_CALENDAR(d) |
YEAR(d) |
EXTRACT(YEAR FROM d) |
|
MONTH(d) |
取月 |
EXTRACT(MONTH FROM d) |
MONTH(d) |
MONTH(d) |
MONTH(d) |
EXTRACT(MONTH FROM d)/TD_MONTH_OF_YEAR(d) |
MONTH(d) |
EXTRACT(MONTH FROM d) |
|
DAY(d) |
取天 |
EXTRACT(DAY FROM d) |
DAY(d) |
DAY(d) |
DAY(d) |
EXTRACT(DAY FROM d)/TD_DAY_OF_MONTH(d) |
DAY(d) |
EXTRACT(DAY FROM d) |
|
HOUR(d) |
取小时 |
EXTRACT(HOUR FROM d) |
DATEPART(HH, d) |
HOUR(d) |
HOUR(d) |
EXTRACT(HOUR FROM d) |
HOUR(d) |
EXTRACT(HOUR FROM d) |
|
MINUTE(d) |
取分钟 |
EXTRACT(MINUTE FROM d) |
DATEPART(MI,d) |
MINUTE(d) |
MINUTE(d) |
EXTRACT(MINUTE FROM d) |
MINUTE(d) |
EXTRACT(MINUTE FROM d) |
|
SECOND(d) |
取秒 |
EXTRACT(SECOND FROM d) |
DATEPART(SS,d) |
SECOND(d) |
SECOND(d) |
CAST(EXTRACT(SECOND FROM d) AS INT) |
SECOND(d) |
EXTRACT(SECOND FROM d) |
|
QUARTER(d) |
取季度 |
FLOOR((EXTRACT(MONTH FROM d)+2)/3) |
DATEPART(QQ,d) |
QUARTER(d) |
QUARTER(d) |
TD_QUARTER_OF_YEAR(d) |
QUARTER(d) |
EXTRACT(QUARTER FROM d) |
|
TODAY() |
今天 |
SYSDATE |
GETDATE()
/CONVERT(DATETIME,CONVERT(CHAR, GETDATE(),101),101)//去掉时分秒 |
CURRENT DATE |
CURDATE() |
DATE |
TODAY |
CURRENT_DATE |
|
NOW() |
当前时间 |
SYSTIMESTAMP |
GETDATE()/CURRENT_TIMESTAMP |
CURRENT TIMESTAMP |
NOW() |
CURRENT_TIMESTAMP |
NOW |
NOW() |
|
ADDYEARS(d,n) |
增加年数 |
d+NUMTOYMINTERVAL(n,'YEAR') |
DATEADD(YY,n,d) |
d+n YEARS |
d+INTERVAL n YEAR |
ADD_MONTHS(d, n*12) |
DATEADD('yy', n, d)/d+n YEAR |
|
|
ADDMONTHS(d, n) |
增加月数 |
d+NUMTOYMINTERVAL(n,'MONTH') |
DATEADD(MM,n,d) |
d+n MONTHS |
d+INTERVAL n MONTH |
ADD_MONTHS(d, n) |
DATEADD('mm', n, d)/d+n MONTH |
|
|
ADDDAYS(d, n) |
增加天数 |
d+NUMTODSINTERVAL(n,'DAY') |
DATEADD(DD,n,d) |
d+n DAYS |
d+INTERVAL n DAY |
d+CAST(n AS INTERVAL DAY) |
DATEADD('dd', n, d)/d+n DAY |
|
|
ADDHOURS(d, n) |
增加小时数 |
d+NUMTODSINTERVAL(n,'HOUR') |
DATEADD(HH,n,d) |
d+n HOURS |
d+INTERVAL n HOUR |
d+CAST(n AS INTERVAL HOUR) |
DATEADD('hh', n, d)/d+n HOUR |
|
|
ADDMINUTES(d, n) |
增加分钟数 |
d+NUMTODSINTERVAL(n,'MINUTE') |
DATEADD(MI,n,d) |
d+n MINUTES |
d+INTERVAL n MINUTE |
d+CAST(n AS INTERVAL MINUTE) |
DATEADD('mi', n, d)/d+n MINUTE |
|
|
ADDSECONDS(d, n) |
增加秒数 |
d+NUMTODSINTERVAL(n,'SECOND') |
DATEADD(SS,n,d) |
d+n SECONDS |
d+INTERVAL n SECOND |
d+CAST(n AS INTERVAL SECOND) |
DATEADD('ss', n, d)/d+n SECOND |
|
|
DAYOFYEAR(d) |
当年中第几天 |
TO_NUMBER(TO_CHAR(d,'DDD')) |
DATEPART(DY,d) |
DAYOFYEAR(d) |
DAYOFYEAR(d) |
TD_DAY_OF_YEAR(d) |
DAYOFYEAR(d) |
EXTRACT(DOY FROM d) |
|
WEEKOFYEAR(d) |
当年中第几星期 |
TO_NUMBER(TO_CHAR(d,'WW')) |
DATEPART(WW,d) |
WEEK(d) |
WEEK(d) |
TD_WEEK_OF_YEAR(d) |
WEEK(d) |
EXTRACT(WEEK FROM d) |
转换函数
|
标准函数 |
含义 |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
ASCII(str) |
串中最左边字符的ASCII码 |
|
|
|
|
|
|
|
|
CHR(n) |
整数n转换成字符 |
CHR(n) |
CHAR(n) |
CHR(n) |
CHAR(n) |
|
CHAR(n) |
|
|
INT(x) |
字符串或数值转成整数 |
CAST(x AS INT) |
CAST(x AS INT) |
INT(x) |
CAST(x AS SIGNED) |
CAST(x AS INT) |
CAST(x AS INT) |
|
|
DECIMAL(x,len,scale) |
字符串或数值转成数值 |
CAST(x AS DECIMAL(len,scale)) |
CAST(x AS DECIMAL(len,scale)) |
DECIMAL(x,len,scale) |
CAST(x AS DECIMAL(len,scale)) |
CAST(x AS DECIAML(len,scale)) |
CAST(x AS DECIAML(len,scale)) |
|
|
TIMESTAMP(str) |
yyyy-mm-dd hh24:mi:ss格式串转换成日期 |
TO_DATE(str,'YYYY-MM-DD HH24:MI:SS') |
CONVERT(DATETIME,str,120) |
TO_DATE(str,'YYYY-MM-DD HH24:MI:SS') |
TIMESTAMP(str) |
TO_TIMESTAMP(str,'YYYY-MM-DD HH24:MI:SS') |
TIMESTAMP(str) |
|
|
NUMTOCHAR(d) |
数值转成字符串 |
TO_CHAR(d) |
CAST(d AS VARCHAR) |
CHAR(d) |
CAST(d AS CHAR) |
TO_CHAR(d) |
CONVERT(d, SQL_VARCHAR) |
CAST(d AS TEXT) |
|
DATETOCHAR(date) |
将日期转化成yyyy-mm-dd hh24:mi:ss格式串 |
TO_CHAR(date,'YYYY-MM-DD HH24:MI:SS') |
CONVERT(CHAR,date,120) |
TO_CHAR(date,'YYYY-MM-DD HH:MI:SS') |
DATE_FORMAT(d,
'%Y-%m-%d %H:%i:%S) |
TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') |
TO_CHAR(d, 'YYYY-MM-DD HH24:MI:SS') |
TO_CHAR(d,'YYYY-MM-DD HH24:MI:SS') |
|
CAST(x, y) |
将x转化为数据类型y(注：y不能使用?) |
CAST(x as y) |
CAST(x as y) |
CAST(x as y) |
CAST(x as y) |
CAST(x as y) |
CAST(x as y) |
|
|
NULLIF(x1,x2) |
若x1=x2返回null，否则返回x1 |
|
|
|
|
|
|
|
|
DATE(str) |
yyyy-mm-dd
格式串转换成日期 |
TO_DATE(DATES,'YYYY-MM-DD') |
CONVERT(varchar(100), DATES, 23) |
DATE(DATES) |
DATE_FORMAT(DATES,'%Y-%m-%d') |
CAST(DATES AS DATE FORMAT 'YYYY-MM-DD') |
|
|
任意函数
|
标准函数 |
含义 |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
CASE(when1,then1, …[,else]) |
when=true则返回相应的then，否则返回else |
CASE WHEN when1 THEN then1 … ELSE else END |
CASE WHEN when1 THEN then1 … ELSE else END |
CASE WHEN when1 THEN then1 … ELSE else END |
CASE WHEN when1 THEN then1 … ELSE else END |
|
CASE WHEN when1 THEN then1 … ELSE else END |
|
|
COALESCE(x1,…) |
返回第一个非null的参数 |
|
|
|
|
|
|
|
|
CONCAT(s1,s2,…) |
连接多个字符串 |
s1||s2||… |
s1+s2+… |
s1||s2||… |
CONCAT(s1,s2,…) |
s1||s2||… |
s1||s2||… |
s1||s2||… |
参数：
|
sql |
sql语句。 |
|
dbtype |
数据库类型。参照表dbtype可直接用名字，找不到时报错。 |
返回值：
SQL语句
示例：
|
|
A |
|
|
1 |
SELECT ID,WEEKOFYEAR(DATES),CUSTOMER,AREA FROM CLUE |
使用标准函数： WEEKOFYEAR(d) : 当年中的第几个星期 |
|
2 |
=A1.sqltranslate("ORACLE") |
返回结果： SELECT ID,TO_NUMBER(TO_CHAR(DATES,'WW')),CUSTOMER,AREA FROM CLUE |
|
3 |
=A1.sqltranslate("SQLSVR") |
返回结果： SELECT ID,DATEPART(WW,DATES),CUSTOMER,AREA FROM CLUE |
|
4 |
=A1.sqltranslate("DB2") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
5 |
=A1.sqltranslate("MYSQL") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
6 |
=A1.sqltranslate("HSQL") |
返回结果： SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
7 |
=A1.sqltranslate("TERADATA") |
返回结果： SELECT ID,TD_WEEK_OF_YEAR(DATES),CUSTOMER,AREA FROM CLUE |
|
8 |
=A1.sqltranslate("POSTGRES") |
返回结果： SELECT ID,EXTRACT(WEEK FROM DATES),CUSTOMER,AREA FROM CLUE |
可对当前SQL标准函数进行扩散，增加新功能，实现对数据库函数的修改和增加。
只需要在function.xml文件中进行配置，该文件位于esproc-bin-xxxx.jar中/com/scudata/dm/sql/路径下，内容格式如下：
其中，FUNCTION节点中，name属性表示标准函数名称；paramcount属性表示函数中的参数个数；value表示数据库中对应的函数语法。
INFO节点中，dbtype属性表示数据库类型；value属性表示数据库中对应的函数写法，此处value为空则默认使用FUNCTION节点中的value的值。
增加自定义函数步骤：
1、定义函数名及其参数个数，在<FUNCTION>节点下配置name及paramcount属性值；
2、在<INFO>子节点下设置数据库类型，dbtype对应数据库类型，value对应数据库的函数。每个FUNCTION节点下可以有多个INFO子节点。
增数据库类型时，用户只需在function.xml中的FUNCTION节点中增加INFO节点，并在该节点中配置dbtype及value属性，dbtype设置为数据库类型，value设置为该数据库的对应函数即可。