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 and POSTGRES. 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) |
Return a substring |
|
LEN(str) |
Return the length of a string |
|
INDEXOF(sub,str[,start]) |
Return the position of a substring |
|
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 |
|
REPLACE(str,sub,rplc) |
Replace a substring by another string |
Numerical 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 |
|
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 |
|
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 number |
Date 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 from d |
|
SECOND(d) |
Return the second |
|
QUARTER(d) |
Return the quarter from 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 |
|
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 numerica 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(str) |
Convert the format string of yyyy-mm-dd hh24:mi:ss into datetime |
|
NUMTOCHAR(d) |
Return a string for number d |
|
DATETOCHAR(date) |
Convert date into a string in the format of yyyy-mm-dd hh24:mi:ss |
|
CAST(x, y) |
Convert x to data type y (note: y can’t be written as ?) |
|
NULLIF(x1,x2) |
Return null if x1=x2, otherwise return x1 |
|
DATE(str) |
Convert the format string of yyyy-mm-dd into a date |
|
CAST(x,y) |
Convert data x to data type y. |
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 |
|
CONCAT(s1,s2,…) |
Concatenate multiple strings |
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 |