sqltranslate()

Read（3741） Like（0） Label: translate, specified,

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) SUBSTR(str,start)	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
WEEKOFYEAR(d)	Return the calendar week of the date

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

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

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.