...

Dynamically Query Tables Whose Names Are Stored in Another Table

 
SQL(179)

【Question】

I have 3 tables: A, B, and C. Table A stores table names except its own. Both table B and table C are associated with Table A through ID field.
Table A:
ID TableName
01 B
02 C
03 B
Table B:
ID Num
01 13
02 14
03 15
Table C:
ID Num
01 91
02 92
03 93
The result I want looks like this:
ID Num
0113
02 92
03 15
Any help would be appreciated.

 

【Answer】

You need a stored procedure to compose a dynamic SQL query. That’s not simple at all. We can write the script in SPL (Structured Process Language). The step-by-step query is intuitive and easy to understand:

A

1

$select ID,TableName from A

2

=A1.group(TableName).(mssql.query("select    ID,Num   from"+TableName+"where ID in  (?)",~.(ID))).conj().sort(ID)

A1: Retrieve data from Table A.

A2: Group A1’s records by TableName, compose a dynamic query to get desired values for each group, and concatenate records of every group and sorted them by ID.

The esProc JDBC interface is like a bridge that connects an SPL script and another application. To learn more about the integration, see How to Call an SPL Script in Java.


SQL(179)
 
 •  389 View  •