dynamic SQL queries with TI parameters
It is possible to use parameters in the SQL statement of Turbo Integrator to produce dynamic ODBC queries
Here is how to proceed:
1. create your TI process
2. Advanced->Parameters Tab, insert parameter p0
3. Advanced->Prolog Tab add the processing code to define parameter p0
p0 = CellGetS(cube,dim1,dim2...)
4. save the TI process
5. open Data Source, add parameter p0 in WHERE clause
select * from lib.table where name = '?p0?'
DO NOT CLICK ON THE VARIABLES TAB AT ANY TIME
6. run, answer "keep all variables" when prompted
If you need numeric parameters, there is a twist! numeric parameters do not work! (at least for TM1 9.x)
select * from lib.customer where purchase > ?p0? will fail although p0 is defined as a numeric and quotes have been removed accordingly.
But fear not, there is a "simple" workaround
1. proceed as above up to step 5
2. Advanced->Prolog tab, at the bottom:
strp0 = NumberToString(p0);
3. Data Source tab, in the SQL statement replace
CAST('?strp0?' as int)
select * from lib.customer where purchase > ?p0?
select * from lib.customer where purchase > CAST('?strp0?' as int)
clicking the preview button will not show anything but the process will work as you can verify by placing an asciioutput in the Advanced->Data tab.
The CAST function is standard SQL so that should be working for any type of SQL server.