Page 1 of 1
TM1 9.4 SQL Procedure within a Process
Posted: Fri Oct 02, 2009 2:35 pm
by rfielden
Newbie here again. Is it possible to run a procedure within a TM1 process to derive variables used in a TM1 spreadsheet? I need to run thru 2-3 processes against an Oracle table(s) to derive a set of variables used in our TM1 spreadsheet. Any advice would be appreciated. Thanks, Rick.
Re: TM1 9.4 SQL Procedure within a Process
Posted: Fri Oct 02, 2009 9:43 pm
by David Usherwood
Yes
In TI, you can specify an ODBC data source. In the query tab you can execute a stored proc in place of a query. (Watch out for parameters - from memory, they need to look like '?p1?' . The server passes the text of the 'query' directly to the relational engine. Sadly if there is a syntax error you are not told what the problem is. The output from the stored proc comes into TI in columnar format, just as if it were a flat file, table or view.
Re: TM1 9.4 SQL Procedure within a Process
Posted: Mon Oct 05, 2009 1:34 pm
by rfielden
Thank you sir and good news. I will give it a try and update this post with results.
Re: TM1 9.4 SQL Procedure within a Process
Posted: Fri Oct 09, 2009 5:47 pm
by rfielden
I tried running the procedure several ways within a process with no luck. I get an OK box when click preview but that is it. Looking at results, nothing was updated per procedure. Any clues where this lowly, pion newbie may be mucking up?
Re: TM1 9.4 SQL Procedure within a Process
Posted: Fri Oct 09, 2009 7:47 pm
by rfielden
Resolved. Needed to use BEGIN....END format for procedure in the process to work.