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.

Q & A Collection

https://stackoverflow.com/questions/63414325/virtual-federated-sql-database