sqltranslate()
Description:
Translate standard SQL functions into functions a specified database defines.
Syntax:
sql.sqltranslate(dbtype)
Note:
The function translates standard SQL functions into functions a specified database defines. Default database types it supports include ORACLE, SQLSVR, DB2, MYSQL, HSQL, TERADATA and POSTGRES. Users are allowed to define user-defined database types and functions by modifying the configuration file.
Standard SQL functions and corresponding functions defined in certain databases:
String functions
|
Standard SQL functions |
Description |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
LOWER(str) |
Convert into lower case |
|
|
|
|
|
|
|
|
UPPER(str) |
Convert into upper case |
|
|
|
|
|
|
|
|
LTRIM(str) |
Delete whitespaces on the leftmost side |
|
|
|
|
|
|
|
|
RTRIM(str) |
Delete whitespaces on the rightmost side |
|
|
|
|
|
|
|
|
TRIM(str) |
Delete whitespaces on both sides |
TRIM(str) |
LTRIM(RTRIM(str)) |
LTRIM(RTRIM(str)) |
TRIM(str) |
TRIM(str) |
|
LTRIM(RTRIM(str)) |
|
SUBSTR(str,start,len) |
Return a substring |
SUBSTR(str,start,len) |
SUBSTRING(str,start,len) |
SUBSTR(str,start,len) |
SUBSTR(str,start,len) |
SUBSTR(str,start,len) |
SUBSTR(str,start,len) |
SUBSTR(str,start,len) |
|
LEN(str) |
Return the length of a string |
LENGTH(str) |
LEN(str) |
LENGTH(str) |
LENGTH(str) |
CHAR_LENGTH(str) |
CHAR_LENGTH(str) |
CHAR_LENGTH(str) |
|
INDEXOF(sub,str[,start]) |
Return the position of a substring |
INSTR(str,sub[,start]) |
CHARINDEX(sub,str[,start]) |
LOCATE(sub,str[,start]) |
LOCATE(sub,str[,start]) |
INDEX(str,sub) |
LOCATE(sub,str[,start]) |
INDEX(str,sub) |
|
LEFT(str,len) |
Return the substring in the left of a specified string |
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) |
Return the substring in the right of a specified string |
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) |
Concatenate two strings |
CONCAT(str1,str2) |
str1+str2 |
CONCAT(str1,str2) |
CONCAT(str1,str2) |
str1||str2 |
str1||str2 |
str1||str2 |
|
REPLACE(str,sub,rplc) |
Replace a substring by another string |
|
|
|
|
OREPLACE(str,sub,rplc) |
|
|
Numeric functions
|
Standard functions |
Description |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
ABS(x) |
Return absolute value |
|
|
|
|
|
|
|
|
ACOS(x) |
Return arc cosine |
|
|
|
|
|
|
|
|
ASIN(x) |
Return arc sine |
|
|
|
|
|
|
|
|
ATAN(x) |
Return arc tangent |
|
|
|
|
|
|
|
|
ATAN2(x,y) |
Return arc tangent |
ATAN2(x,y) |
ATN2(x,y) |
ATAN2(x,y) |
ATAN2(x,y) |
|
|
|
|
CEIL(x) |
Return the smallest integer that is not less than x |
CEIL(x) |
CEILING(x) |
CEIL(x)/CEILING(x) |
CEIL(x)/CEILING(x) |
CEIL(x) |
|
CEIL(x) |
|
COS(x) |
Return cosine value |
|
|
|
|
|
|
|
|
EXP(x) |
Return the base of e to the power of x |
|
|
|
|
|
|
|
|
FLOOR(x) |
Return the biggest integer that not greater than x |
|
|
|
|
|
|
|
|
LN(x) |
Return the natural logarithm |
LN(x) |
LOG(x) |
LN(x)/LOG(x) |
LN(x)/LOG(x) |
LN(x) |
|
|
|
LOG10(x) |
Return the logarithm with base 10 |
LOG(10,x) |
LOG10(x) |
LOG10(x) |
LOG10(x) |
LOG(x) |
LOG10(x) |
LOG(x) |
|
MOD(x,m) |
Return the remainder of x divided by m |
MOD(x,m) |
x%m |
MOD(x,m) |
x%m |
x MOD m |
|
x%m |
|
POWER(x,y) |
Return the value of x raised to the power of y |
|
|
|
|
|
|
|
|
ROUND(x,n) |
Return x rounded to n digits from the decimal point |
|
|
|
|
|
|
|
|
SIGN(x) |
Return the sign for x |
|
|
|
|
|
|
|
|
SIN(x) |
Return the sine value |
|
|
|
|
|
|
|
|
SQRT(x) |
Return the square root of x |
|
|
|
|
|
|
|
|
TAN(x) |
Return the tangent value |
|
|
|
|
|
|
|
|
TRUNC(x,n) |
Return x truncated to n decimal places |
TRUNC(x,n) |
CONVERT(BIGINT,x*POWER(10,n))/POWER(10.0,n) |
TRUNC(x,n) |
TRUNCATE(x,n) |
TRUNC(x,n) |
|
|
|
RAND(seed) |
Return a random number |
DBMS_RANDOM.VALUE(0, seed) |
RAND(seed)// Return a random number between 0 and 1 |
RAND(seed)// Return a random number between 0 and 1 |
RAND(seed) |
RANDOM(0,seed) |
RAND(seed) |
RANDOM() |
Date functions
|
Standard functions |
Description |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
YEAR(d) |
Return the year |
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) |
Return the month |
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) |
Return the day of the month |
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) |
Return the hour |
EXTRACT(HOUR FROM d) |
DATEPART(HH, d) |
HOUR(d) |
HOUR(d) |
EXTRACT(HOUR FROM d) |
HOUR(d) |
EXTRACT(HOUR FROM d) |
|
MINUTE(d) |
Return the minute from d |
EXTRACT(MINUTE FROM d) |
DATEPART(MI,d) |
MINUTE(d) |
MINUTE(d) |
EXTRACT(MINUTE FROM d) |
MINUTE(d) |
EXTRACT(MINUTE FROM d) |
|
SECOND(d) |
Return the second |
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) |
Return the quarter from 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() |
Return the date |
SYSDATE |
GETDATE() |
CURRENT DATE |
CURDATE() |
DATE |
TODAY |
CURRENT_DATE |
|
NOW() |
Return the current time |
SYSTIMESTAMP |
GETDATE()/CURRENT_TIMESTAMP |
CURRENT TIMESTAMP |
NOW() |
CURRENT_TIMESTAMP |
NOW |
NOW() |
|
ADDYEARS(d,n) |
Add years |
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) |
Add months |
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) |
Add days |
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) |
Add hours |
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) |
Add minutes |
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) |
Add seconds |
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) |
Return the day of year |
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) |
Return the calendar week of the date |
TO_NUMBER(TO_CHAR(d,'WW')) |
DATEPART(WW,d) |
WEEK(d) |
WEEK(d) |
TD_WEEK_OF_YEAR(d) |
WEEK(d) |
EXTRACT(WEEK FROM d) |
Conversion functions
|
Standard functions |
Description |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
ASCII(str) |
Return numeric value of left-most character |
|
|
|
|
|
|
|
|
CHR(n) |
Return the chracter for integer n |
CHR(n) |
CHAR(n) |
CHR(n) |
CHAR(n) |
|
CHAR(n) |
|
|
INT(x) |
Return an integer for string or number 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) |
Return the numeric value for a string or number |
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) |
Convert the format string of yyyy-mm-dd hh24:mi:ss into datetime |
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) |
Return a string for number 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) |
Convert date into a string in the format of 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, |
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) |
Convert x to data type y (note: y can’t be written as ?) |
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) |
Return null if x1=x2, otherwise return x1 |
|
|
|
|
|
|
|
|
DATE(str) |
Convert the format string of yyyy-mm-dd into a date |
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') |
|
|
Other functions
|
Standard functions |
Description |
oracle |
sql server |
db2 |
mysql |
teradata |
hsql |
PostgresSQL |
|
CASE(when1,then1, …[,else]) |
Return the corresponding then part if when is true, otherwise return the else part |
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,…) |
Return the first non-null parameter |
|
|
|
|
|
|
|
|
CONCAT(s1,s2,…) |
Concatenate multiple strings |
s1||s2||… |
s1+s2+… |
s1||s2||… |
CONCAT(s1,s2,…) |
s1||s2||… |
s1||s2||… |
s1||s2||… |
Parameter:
|
sql |
A SQL statement |
|
dbtype |
Database type, which just uses the type name in the referencing table; error will be reported when the type name cannot be found |
Return value:
A SQL statement
Example:
|
|
A |
|
|
1 |
SELECT ID,WEEKOFYEAR(DATES),CUSTOMER,AREA FROM CLUE |
Use a standard SQL function. WEEKOFYEAR(d): Find the calendar week of the year. |
|
2 |
=A1.sqltranslate("ORACLE") |
Return the following SQL statement: SELECT ID,TO_NUMBER(TO_CHAR(DATES,'WW')),CUSTOMER,AREA FROM CLUE
|
|
3 |
=A1.sqltranslate("SQLSVR") |
Return the following SQL statement: SELECT ID,DATEPART(WW,DATES),CUSTOMER,AREA FROM CLUE |
|
4 |
=A1.sqltranslate("DB2") |
Return the following SQL statement: SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
5 |
=A1.sqltranslate("MYSQL") |
Return the following SQL statement: SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
6 |
=A1.sqltranslate("HSQL") |
Return the following SQL statement: SELECT ID,WEEK(DATES),CUSTOMER,AREA FROM CLUE |
|
7 |
=A1.sqltranslate("TERADATA") |
Return the following SQL statement: SELECT ID,TD_WEEK_OF_YEAR(DATES),CUSTOMER,AREA FROM CLUE |
|
8 |
=A1.sqltranslate("POSTGRES") |
Return the following SQL statement: SELECT ID,EXTRACT(WEEK FROM DATES),CUSTOMER,AREA FROM CLUE |
User-defined functions
esProc allowed to extend functionalities of the existing SQL standard functions. So you can modify database functions or add new members to them. To do this you need to do the configuraitons in function.xml, which is located in directory /com/scudata/dm/sql/esproc-bin-xxxx.jar. Its content is shown below:
Under each FUNCTION node, name property is the name of a standard SQL function;
paramcount property specifies the number of parameters in the function; value property is the syntax of counterpart function in the target database;
Under each INFO subnode, dbtype property is database type name;
value property is the syntax of the function implemented by this database; default is value property value under FUNCTION node if it isn’t defined.
Directions for defining a user-defined
function:
1. Specify function name and number of parameters through name property
and paramcount property under <FUNCTION> node;
2. Configure database type under <INFO> subnode. The dbtype is
database type name, and value is the corresponding function in the
specified database. There can be multiple INFO subnodes under FUNCTION node.
Add new database type
To add a new database type, you can add an INFO subnode under FUNCTION node in function.xml and configure dbtype property and value property. The dbtype is database type name, and value is the corresponding function in the specified database.