Dynamic SQL queries with TI parametersDynamic SQL queries with TI parameters admin Mon, 2019-11-18 15:40
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.
Note from Norman Bobo:
It is not necessary to add the "CAST" function to the SQL to pass numeric values. The trick is to think of the parameter as a substitution string, not a value. Define the parameter as a string and insert it into the SQL embedded in ?'s. When setting the value in the Advanced Prolog tab, convert the numeric value you would like to use into a string value. That value will be simply substituted into the SQL statement directly, as if you typed the value into the SQL yourself.
In the Advanced/Prolog tab:
strFY = NUMBERTOSTRING(CELLGETN('Control Cube','FYElem','ControlValue');
Name: strFY, datatype: string, Default value: 2010, Prompt: Enter the FY:
In the SQL:
where FY = ?strFY?