Embedded SQL in Datawindow script 2

[girls3dog1]

For a simple SQL query I'd like to have the table name be a variable such that I can change the table name during runtime. I guess the advantage is that I just have to write the SQL once as I have many tables to query with the same where clause. To date, I've only had experience with tbl_name being the actual name of the database table, which of course works just fine.

Ex. Select column_X, column_Y, column_Z
From tbl_name *
Where column_X = "ABC"

* tbl_name would be local string variable that I would set in the script before I hit the Select statement. Is this possible in SQL?

 

[rt63]

You can use the modify method to change the select statement of the datawindow at run time as follows

1. Design your datawindow with any table so that the columns of the datawindow are named as column_x, column_y and column_z.
2. During run time, you decide that the query should run from say tbl_name_a. So you can write the code as follows

string ls_select , ls_table
ls_table = 'tbl_name_a' // assuming you have avariable which will give the table name
ls_select = 'DataWindow.Table.Select="select column_x, column_y, column_z from '+ls_table+' where column_x = '+"'ABC'"+'"'
your_dw.Modify(ls_select)
your_dw.Retrieve() // and so on

You have to be a bit careful with the single and double quotes.

Hope it helps.
RT

 

[mbalent]

You can also change the sql in the sqlpreview event of the datawindow.