How to Access Databases using One SQL Statement
Problem description & analysis
There is a sales data table in Oracle. The table stores data of the year 2013, as shown below:
| ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
| 57 |
JAXE |
5 |
17300 |
2013/1/2 |
| 60 |
PWQ |
16 |
3430 |
2013/1/5 |
| 61 |
SJCH |
19 |
1078 |
2013/1/8 |
| 62 |
JAXE |
11 |
8134 |
2013/1/6 |
| 63 |
SJCH |
16 |
5880 |
2013/1/10 |
| … |
… |
… |
… |
… |
There is also a sales data table in MySQL. It stores data of the year 2014, as shown below:
| ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
| 422 |
JOPO |
9 |
392 |
2014/1/1 |
| 424 |
AYWYN |
13 |
25800 |
2014/1/4 |
| 428 |
SAVEA |
8 |
11600 |
2014/1/9 |
| 429 |
PJIPE |
14 |
29400 |
2014/1/5 |
| 430 |
PJIPE |
10 |
7742 |
2014/1/12 |
| … |
… |
… |
… |
… |
We are trying to get data of the 35th week from both sales tables and concatenate the results. Below is the desired result:
| ORDERID |
CLIENT |
SELLERID |
AMOUNT |
ORDERDATE |
| 294 |
YZ |
9 |
24400 |
2013/8/27 |
| 295 |
PJIPE |
16 |
12200 |
2013/8/27 |
| 296 |
PJIPE |
2 |
7448 |
2013/8/27 |
| 298 |
AVU |
2 |
9604 |
2013/8/27 |
| 299 |
UJRNP |
4 |
5586 |
2013/9/1 |
| 300 |
HANAR |
17 |
2842 |
2013/8/26 |
| 302 |
AYWYN |
6 |
4214 |
2013/9/1 |
| 303 |
JOPO |
6 |
26800 |
2013/8/31 |
| 659 |
JAXE |
20 |
23900 |
2014/8/27 |
| 660 |
QHHW |
5 |
13500 |
2014/8/29 |
| 662 |
JOPO |
17 |
9114 |
2014/8/31 |
| 664 |
SJCH |
1 |
27400 |
2014/8/28 |
| 665 |
UJRNP |
15 |
13400 |
2014/8/27 |
| 669 |
JAXE |
20 |
28800 |
2014/8/31 |
| 670 |
JAYB |
17 |
19300 |
2014/8/31 |
Solution
Write the following script p1.dfx in esProc:
| A |
|
| 1 |
>sql="SELECT * FROM sales WHERE WEEKOFYEAR(orderdate) = 35" |
| 2 |
>dbname=["oracle","mysql"] |
| 3 |
>dbtype=["ORACLE","MYSQL"] |
| 4 |
=dbname.(connect@l(~)) |
| 5 |
=dbtype.(sql.sqltranslate(~)) |
| 6 |
=A4.conj(~.query@x(A5(#))) |
Explanation
A1 The standard SQL to be executed.
A2 Define a sequence of data source names.
A3 Define a sequence of data source types.
A4 Connect to each database.
A5 Translate functions in the standard SQL statement into their counterparts in the specific databse.
A6 Concatenate results returned from databases in which the corresponding SQL is executed.
Read How to Call an SPL Script in Java to learn how to integrate the script code into a Java program.
https://stackoverflow.com/questions/63414325/virtual-federated-sql-database
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
