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
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
