TM1 Forum

Hi Guys.....
I need your help.
How can i build a dynamic argument query in TI.
Let me explain:

1. I need connect to iseries tables through ODBC connection.
2. In the argument "query" of the TI, the string of query i need build of dynamic string, with the parameters received in TI


Anybody knows about that.....

roguerrero wrote:Hi Guys.....
I need your help.
How can i build a dynamic argument query in TI.
Let me explain:

1. I need connect to iseries tables through ODBC connection.
2. In the argument "query" of the TI, the string of query i need build of dynamic string, with the parameters received in TI

Anybody knows about that.....

- Create the SQL query as normal.
- For any parts of the query which need to be dynamically changed, replace the actual SQL text with parameter names surrounded with question marks. For example, if the year is to be specified dynamically, swap the hard coded year with something like:
?FromYr?
- Create parameters with those names in the Advanced tab.
- In the Prolog tab, assign values to the parameters. This would typically be done by reading entries in a control cube, say, so that the FromYr is, for example:
FromYr = CellGetS( SC_CUBE_CONTROL, 'FromYear', SC_CHORE_NAME);
- When you run the process, the FromYr will be replaced with the value that has been read.

CAUTION: You need to specify default values for the parameters at both process and chore level. If your parameters are strings (and they're likely to be in this case), when you enter the default values at Chore level make sure that you enter dummy values which can only be evaluated as text. If it's a year value, don't enter '2009', enter something like 'Year'. Otherwise it'll cause an error on startup because TI will try to convert the default into a number. You'll probably never use the default, so it doesn't matter.

Thanks for your reply.....
I will tray ....

Hey guys, I have a question for you !

How can I create a dynamic query based on a numeric parameter ? The following code works but I always have a "SQL STATEMENT FAIL" error message (even if i'm able to run my TI successfully). Any Idea how to bypass the error message ?
Thanks

Emixam wrote: ↑Thu Aug 01, 2019 1:34 pm Hey guys, I have a question for you !

How can I create a dynamic query based on a numeric parameter ? The following code works but I always have a "SQL STATEMENT FAIL" error message (even if i'm able to run my TI successfully). Any Idea how to bypass the error message ?

Code: Select all

SELECT *
From A_TABLE
WHERE NUMERIC_DATE <= '?nDate?'

Thanks

Why are you encapsulating the ?nDate? in apostrophes? That is telling SQL the value you are sending is a string.

I didn't know that. But even if I remove the '', I still get the error message.

Hi,

Try making nDate a string and then cast in the sql code

So, WHERE NUMERIC_DATE <= cast('?nDate?' as whateveryouneedtocastitto)

cheers, Mark

My preference is to set the whole query up in the local variable DataSourceQuery and then write it out to a file for debugging purposes eg:

We use EXPAND. All of our SQL queries are stored in a cube so we do not have to go through an onerous code migration of a TI if/when a SQL query needs tweaking. If TI needs a particular SQL query, we have utility TI that "assembles" the SQL query, runs it through an EXPAND and we get the SQL we need, completely dynamic.

David Usherwood wrote: ↑Thu Aug 01, 2019 6:02 pm My preference is to set the whole query up in the local variable DataSourceQuery and then write it out to a file for debugging purposes eg:

Code: Select all

datasourcequery = 'SELECT *
From A_TABLE
WHERE NUMERIC_DATE <= ' | nDate
;
asciioutput(test.csv',datasourcequery);

Thanks David, that's what I did and it works perfectly !

Thanks guys for your answer !