sqltranslate()

Read(2857) Label: translate, specified,

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)
SUBSTR(str,start)

Return a substring

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)

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)
Return n/a if there are 3 parameters

LOCATE(sub,str[,start])

INDEX(str,sub)
Return n/a if there are 3 parameters

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)
//Return random numbers between 0 and 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)
// Return a random integer between 0 an seed

RAND(seed)
//Return a random number between 0 and 1

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()
/CONVERT(DATETIME,CONVERT(CHAR, GETDATE(),101),101)//Remove the hour, minute and second

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,
 '%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)

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.