Dynamic query argument in TI...
-
- Posts: 6
- Joined: Mon Sep 29, 2008 2:02 pm
Dynamic query argument in TI...
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.....
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.....
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Dynamic query argument in TI...
- Create the SQL query as normal.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.....
- 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 6
- Joined: Mon Sep 29, 2008 2:02 pm
Re: Dynamic query argument in TI...
Thanks for your reply.....
I will tray ....
I will tray ....
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Dynamic query argument in TI...
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
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?'
-
- MVP
- Posts: 2831
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Dynamic query argument in TI...
Why are you encapsulating the ?nDate? in apostrophes? That is telling SQL the value you are sending is a string.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 ?
ThanksCode: Select all
SELECT * From A_TABLE WHERE NUMERIC_DATE <= '?nDate?'
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Dynamic query argument in TI...
I didn't know that. But even if I remove the '', I still get the error message.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Dynamic query argument in TI...
Hi,
Try making nDate a string and then cast in the sql code
So, WHERE NUMERIC_DATE <= cast('?nDate?' as whateveryouneedtocastitto)
cheers, Mark
Try making nDate a string and then cast in the sql code
So, WHERE NUMERIC_DATE <= cast('?nDate?' as whateveryouneedtocastitto)
cheers, Mark
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: Dynamic query argument in TI...
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);
- PavoGa
- MVP
- Posts: 616
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Dynamic query argument in TI...
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.
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Dynamic query argument in TI...
Thanks David, that's what I did and it works perfectly !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 guys for your answer !