9.22 Translate standard SQL statement into syntax of the specified database

 

Translate a standard SQL statement into the syntax format a specified database uses.
A company’s sales data is stored as two parts. One is in database oracle and the other is in database mysql. And we are trying to count orders whose amounts are above 1,000 from 2015-03-18 to 2015-07-18. The two sales files have same structure as follows:

ORDERID CUSTOMERID EMPLOYEEID ORDERDATE AMOUNT
10400 EASTC 1 2014/01/01 3063.0
10401 HANAR 1 2014/01/01 3868.6
10402 ERNSH 8 2014/01/02 2713.5
10403 ERNSH 4 2014/01/03 1005.9
10404 MAGAA 2 2014/01/03 1675.0

sql.sqltranslate(dbtype) is used to translate functions in a standard SQL statement into their counterparts in a specified database.

SPL script:

A
1 select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE between date(‘2015-03-18’) and date(‘2015-07-18’) and AMOUNT>1000
2 =A1.sqltranslate(“ORACLE”)
3 =A1.sqltranslate(“MYSQL”)
4 =connect(“oracle”).query@x(A2)
5 =connect(“mysql”).query@x(A3)
6 =[A4,A5].merge@ou(ORDERID)
7 =A6.len()

A1 A standard SQL statement.
A2 Translate the standard SQL into ORACLE syntax format.
A3 Translate the standard SQL into MYSQL syntax format.
A4 Connect to oracle and execute SQL.
A5 Connect to mysql and execute SQL.
A6 Merge results of A4 and A5 and remove order records having same IDs.
A7 Count orders.

Execution result:

Value
63

Standard SQL(A1):

select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE  between date('2015-03-18') and date('2015-07-18') and AMOUNT>1000

ORACLE(A2):

select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE  between TO_DATE('2015-03-18','YYYY-MM-DD') and TO_DATE('2015-07-18','YYYY-MM-DD') and AMOUNT>1000


MYSQL(A3):

select ORDERID,CUSTOMERID,EMPLOYEEID,ORDERDATE,AMOUNT from ORDERS where ORDERDATE  between DATE_FORMAT('2015-03-18','%Y-%m-%d') and DATE_FORMAT('2015-07-18','%Y-%m-%d') and AMOUNT>1000