...

Dynamic SQL Query

 
SQL(179)

【Question】

I have the following query:

SELECT****DISTINCT ORIGFROM VIEW_AWHERE $P{P_Info_Type}=1

UNIONSELECTDISTINCT ORIGFROM VIEW_BWHERE $P{P_Info_Type}=2

UNIONSELECTDISTINCT ORIGFROM VIEW_CWHERE $P{P_Info_Type}=3

UNIONSELECTDISTINCT ORIGFROM VIEW_DWHERE $P{P_Info_Type}=4

UNIONSELECTDISTINCT ORIGFROM VIEW_EWHERE $P{P_Info_Type}=5

UNIONSELECTDISTINCT ORIGFROM VIEW_FWHERE $P{P_Info_Type}=6

UNIONSELECTDISTINCT ORIGFROM VIEW_GWHERE $P{P_Info_Type}=7

UNIONSELECTDISTINCT ORIGFROM VIEW_HWHERE $P{P_Info_Type}=8

How can I make it a dynamic query?

Update: Each query would be executed according to the value of parameter P_Info_Type.

Thank you for the help.

 

【Answer】

To make a dynamic query, you can store the table numbers and table names in variables, such as two-dimensional tables, hash tables or temporary tables. Then you can get a table by its number and perform the SQL query dynamically. This can be achieved in SPL (Structured Process Language):

A

1

=[1,“VIEW_A”,2,“VIEW_B”,3,“VIEW_C”,4,“VIEW_D”]  

2

=create(key,value).record(A1)

3

=connect(“demo”)

4

=A3.query("select   distint ORIG FROM"+A2.select(key==argkey).value)

A2’s result:

undefined

A1: Store variables as a sequence;

A2: Create a table sequence and populate records to it;

A3: Connect to a database;

A4: Perform a dynamic query via parameter argkey.


SQL(179)
 
 •  347 View  •