Dynamic query argument in TI...

Post Reply
roguerrero
Posts: 6
Joined: Mon Sep 29, 2008 2:02 pm

Dynamic query argument in TI...

Post by roguerrero »

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.....
User avatar
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...

Post by Alan Kirk »

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.
"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.
roguerrero
Posts: 6
Joined: Mon Sep 29, 2008 2:02 pm

Re: Dynamic query argument in TI...

Post by roguerrero »

Thanks for your reply.....
I will tray ....
Emixam
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...

Post by Emixam »

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
tomok
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...

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Emixam
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...

Post by Emixam »

I didn't know that. But even if I remove the '', I still get the error message.
Mark RMBC
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...

Post by Mark RMBC »

Hi,

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

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

cheers, Mark
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Dynamic query argument in TI...

Post by David Usherwood »

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);
User avatar
PavoGa
MVP
Posts: 612
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: Cleveland, Tennessee

Re: Dynamic query argument in TI...

Post by PavoGa »

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
Emixam
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...

Post by Emixam »

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 !
Post Reply