sqltranslate()
Description:
Translate standard SQL functions in a SQL statement to the dialect of a specified database.
Syntax:
sql.sqltranslate(dbtype)
Note:
The function translates standard SQL functions to the dialect of a database specified by dbtype.
Here standard functions are those used in SQL statements in a SQL script. sqltranslate() translates a SQL statement by converting the standard functions to counterparts of the specified database according to configuration file function.xml.
Parameter dbtype represents database type. Default database types SPL supports include ORACLE, SQLSVR, DB2, MYSQL, HSQL, TERADATA, POSTGRES, DUCKDB, and BIGQUERY. Standard function SPL offers by default are listed below. Users are allowed to define database types and functions as needed by modifying the above-mentioned configuration file.
Standard SQL functions:
String functions
|
Standard SQL functions |
Description |
|
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. |
|
SUBSTR(str,start[,len]) |
Starting from the startth character in string str, get a substring whose length is specified by parameter len; When len is absent, get the substring from the specified character through to end. |
|
LEN(str) |
Return the length of a string. |
|
INDEXOF(sub,str[,start]) |
In string sub, search for substring str starting from the startth character from the left and return its position. Default value of n is 1. The function is case-sensitive. |
|
IPOS(str1,str2[,n]) |
In string str1, search for substring str2 starting from the nth character from the left and return the position of str2. Default value of n is 1. the function is case-insensitive. |
|
GETSUBS(str1,str2[,n]) |
In string str1, search for the nth substring str2 starting from the left and return the substring after str2. Default value of n is 1. |
|
IGETSUBS (str1,str2[,n]) |
In string str1, search for the nth substring str2 starting from the left and return the substring after str2. Default value of n is 1; the function is case-insensitive. |
|
LEFT(str,len) |
Return the substring in the left of a specified string. |
|
RIGHT(str,len) |
Return the substring in the right of a specified string. |
|
CONCAT(str1,str2) |
Concatenate two strings. |
|
CONCAT(s1,s2,…) |
Concatenate multiple strings. |
|
CONCAT_WS(d,str1,str2,…) |
Concatenate multiple strings with delimiter d. |
|
REPLACE(str,sub,rplc) |
Replace a substring by another string. |
|
ISDIGIT(str) |
Judge whether parameter str consists entirely of digits. |
|
ISALPHA(str) |
Judge whether parameter str consists entirely of letters. |
Numeric functions
|
Standard functions |
Description |
|
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. |
|
CEIL(x) |
Return the smallest integer that is not less than x. |
|
CEIL(x,n) |
Round x up according to precision n. Default value of n is 0. Keep n decimal places if n>0; round up to the nearest whole number if n=0; round up to the nearest tens, hundreds, thousands, or other integer place values if n<0. |
|
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. |
|
FLOOR(x,n) |
Round x down according to precision n. Default value of n is 0. Keep n decimal places if n>0; round down to the nearest whole number if n=0; round down to the nearest tens, hundreds, thousands, or other integer place values if n<0. |
|
LN(x) |
Return the natural logarithm. |
|
LOG10(x) |
Return the logarithm with base 10. |
|
MOD(x,m) |
Return the remainder of x divided by 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. |
|
RAND(seed) |
Return a random integer between 0 and seed. |
|
RAND() |
Return a random floating-point number between 0 and 1. |
|
PI() |
Calculate the value of pi. |
Time-related functions
|
Standard functions |
Description |
|
YEAR(d) |
Return the year. |
|
MONTH(d) |
Return the month. |
|
DAY(d) |
Return the day of the month. |
|
HOUR(d) |
Return the hour. |
|
MINUTE(d) |
Return the minute part of d. |
|
SECOND(d) |
Return the second. |
|
QUARTER(d) |
Return the quarter part of d. |
|
TODAY() |
Return the date. |
|
NOW() |
Return the current time. |
|
ADDYEARS(d,n) |
Add years. |
|
ADDMONTHS(d, n) |
Add months. |
|
ADDDAYS(d, n) |
Add days. |
|
ADDHOURS(d, n) |
Add hours. |
|
ADDMINUTES(d, n) |
Add minutes. |
|
ADDSECONDS(d, n) |
Add seconds. |
|
DAYOFYEAR(d) |
Return the day of year. |
|
DAYOFWEEK(d) |
Return the day of week; 1 represents Sunday. |
|
WEEKOFYEAR(d) |
Return the calendar week of the date. |
|
YEARSDIFF(d1,d2) |
Calculate the number of years between two dates. |
|
MONTHSDIFF(d1,d2) |
Calculate the number of months between two dates. |
|
DAYSDIFF(d1,d2) |
Calculate the number of days between two dates. |
|
HOURSDIFF(d1,d2) |
Calculate the number of hours between two dates. |
|
MINUTESDIFF(d1,d2) |
Calculate the number of minutes between two dates. |
|
SECONDSDIFF(d1,d2) |
Calculate the number of seconds between two dates. |
|
TRUNCHOUR(d) |
Truncate to the hour. |
|
TRUNCMINUTE(d) |
Truncate to the minute. |
|
TRUNCSECOND(d) |
Truncate to the second. |
|
MONTHSTART(d) |
Get the first day of the current month. |
|
MONTHEND(d) |
Get the last day of the current month. |
|
WEEKSTART(d) |
Get the first day of the current week, where Monday is the start day. |
|
WEEKEND(d) |
Get the last day of the current week, where Monday is the start day. |
|
QUARTERSTART(d) |
Get the first day of the current quarter. |
|
QUARTEREND(d) |
Get the last day of the current quarter. |
|
YEARSTART(d) |
Get the first day of the current year. |
|
YEAREND(d) |
Get the last day of the current year. |
|
MONTHDAYS(d) |
Calculate the number of days in the current month. |
|
QUARTERDAYS(d) |
Calculate the number of days in the current quarter. |
|
YEARDAYS(d) |
Calculate the number of days in the current year. |
|
ITX(s) |
Generate a time interval constant according to the time component; s represents second. |
|
ITX (null,d) |
Generate a time interval constant according to the time component; d represents day; value of the first parameter is always null. |
|
ITX(null,d,m) |
Generate a time interval constant according to the time component; d represents day and m represents month; value of the first parameter is always null. |
|
ADDX(t,k) |
Perform addition operation between two dates. k can be a time interval constant returned by ITX(). When t is a date/datetime value and k is a numeric value, k represents the day. Perform the addition operation when both t or k is of any of the other data type. |
|
SUBX(t1,t2) |
Perform subtraction operation between two dates. When t1 is a date/datetime value and t2 is a numeric value, t2 represents the day. Perform the subtraction operation when both t1 or t2 is of any of the other data type. |
|
CMPX(it1,it2) |
Perform comparison between two dates. it1 and it2 can be a date, time, datetime, time interval constant, or numeric value. |
Conversion functions
|
Standard functions |
Description |
|
ASCII(str) |
Return numeric value of left-most character. |
|
CHR(n) |
Return the character for integer n. |
|
INT(x) |
Return an integer for string or number x. |
|
DECIMAL(x,len,scale) |
Return the numeric value for a string or number. |
|
TIMESTAMP(d[,str]) |
Convert d to the datetime type according to the format string str, which by default is yyyy-MM-dd HH:mm:ss.. |
|
NUMTOCHAR(d) |
Return a string for number d. |
|
DATETOCHAR(date) |
Convert a date to a string whose format is yyyy-MM-dd HH:mm:ss. |
|
NULLIF(x1,x2) |
Return null if x1=x2, otherwise return x1. |
|
DATE(sd[,fd]) |
Convert sd to the date type according to format string fd, which is by default yyyy-MM-dd. |
|
DATE(y,m,d) |
Convert integers y,m,d to date type; return null if the conversion fails. |
|
TIME(sdt[,fd]) |
Convert sdt to the time type according to format string fd, which is by default HH:mm:ss. |
|
TIME(h,m,s) |
Convert integers h,m,s to time type. |
|
CAST(x,y) |
Convert data x to data type y. |
|
STRING(x[,f]) |
Convert x to the string type. f is the format string according to which the result is formatted, and can be omitted. |
|
CHN(n) |
Convert number n to the corresponding Chinese numeral representation. |
Other functions
|
Standard functions |
Description |
|
CASE(when1,then1, …[,else]) |
Return the corresponding then part if when is true, otherwise return the else part. |
|
COALESCE(x1,…) |
Return the first non-null parameter. |
|
NVL(x1,…) |
Return the first non-null, non-empty string parameter. |
|
NULLIF(x1,x2) |
If x1=x2, return null; otherwise, return x1. |
Parameter:
|
sql |
A SQL statement to be translated. |
|
dbtype |
Database type; translate the database type literally when there are not settings for the current type in function.xml. . |
Return value:
SQL statement
Example:
|
|
A |
|
|
1 |
SELECT ID,WEEKOFYEAR(DATES),CUSTOMER,AREA FROM CLUE |
A SQL statement using standard function WEEKOFYEAR(d); below are settings of WEEKOFYEAR function in function.xml:
|
|
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 allows users to extend functionalities of the existing SQL standard functions by configuring function.xml so that they can modify database functions or add new members to them.
function.xml is located in /com/scudata/dm/sql/esproc-bin-xxxx.jar (esproc-xxxx.jar for the Community Edition). Its content is as follows:
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. dbtype is
database type, whose value must be uppercase, and value is the corresponding
function in the specified database. There can be multiple INFO subnodes under
FUNCTION node.
Add new database type
1. 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.
2. The following database types are supported at present:
|
ORACLE |
SQLSVR |
SYBASE |
SQLANY |
|
INFMIX |
FOXPRO |
ACCESS |
FOXBAS |
|
DB2 |
MYSQL |
KINGBASE |
DERBY |
|
HSQL |
TERADATA |
POSTGRES |
DATALOGIC |
|
IMPALA |
HIVE |
GREENPLUM |
DBONE |
|
ESPROC |
DAMENG |
ESPROC_SQL |
ESSBASE |
|
DUCKDB |
BIGQUERY |
|
|