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 » Wed Feb 25, 2009 8:35 pm

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: 6002
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Dynamic query argument in TI...

Post by Alan Kirk » Wed Feb 25, 2009 9:17 pm

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 » Wed Feb 25, 2009 10:11 pm

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

Emixam
Posts: 40
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: Dynamic query argument in TI...

Post by Emixam » 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

tomok
MVP
Posts: 2642
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 » Thu Aug 01, 2019 2:29 pm

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: 40
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: Dynamic query argument in TI...

Post by Emixam » Thu Aug 01, 2019 2:34 pm

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

Mark RMBC
Regular Participant
Posts: 204
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 » Thu Aug 01, 2019 5:36 pm

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: 1377
Joined: Wed May 28, 2008 9:09 am

Re: Dynamic query argument in TI...

Post by David Usherwood » 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);

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Dynamic query argument in TI...

Post by PavoGa » Fri Aug 02, 2019 12:48 pm

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: 40
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: Dynamic query argument in TI...

Post by Emixam » Fri Aug 02, 2019 8:09 pm

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