TSQL limitations in TI

[OwlHelp]

Hi all,

My SQL query contains a number of parts that prevent it from previewing in the data source tab.

My understanding is that ODBCOpen/Output/Close can be used to directly run SQL queries with minimal TM1 code.

Are there any limitations in terms of what SQL commands are possible in TI- e.g. declaring variables, creating/dropping temp tables, executing functions?

If data is pulled in the prolog tab, what needs to be in Data Source?

[tomok]

My understanding is that ODBCOpen/Output/Close can be used to directly run SQL queries with minimal TM1 code.

I'd be curious to know where you got that understanding. Data pulled in from an ODBC data source still must go through the confines of the Data tab in order to be made available to the TI process. You can't run an SQL query and assign the results to a variable in the Prolog tab. Something like this may be possible by writing a custom function in Java and calling that function from a TI but it's pretty new functionality and I've never tried it.

[OwlHelp]

Tomok wrote:
I'd be curious to know where you got that understanding. Data pulled in from an ODBC data source still must go through the confines of the Data tab in order to be made available to the TI process. You can't run an SQL query and assign the results to a variable in the Prolog tab. Something like this may be possible by writing a custom function in Java and calling that function from a TI but it's pretty new functionality and I've never tried it.

Looks like I misunderstood. Sorry, I'm fairly new to TM1.

I was looking at a related thread several years ago-

adma76 wrote:
Hi guys,
does TI support SQL commands such as :

INNER JOIN
LEFT OUTER JOIN

Thanks in advanced,

Alan Kirk wrote:
Important thing to understand about that... TI does nothing with SQL commands. All they are to TI is a string of text. The SQL commands are simply passed to the SQL database (typically via an ODBC connection) and processed by that database, which then returns the records (if any) to TI for processing. Accordingly TI doesn't care at all about the SQL syntax as such; all that matters is that the database system that you're connecting to supports the commands and syntax that you incorporate into the TI process.

So if TI supports all SQL commands, why does no preview show in the data source tab for cases where certain functions are used? Is the data still being pulled, but not previewed, or do changes need to be made in prolog for the data source query to be accepted?

[Andy Key]

You haven't said what you think it is that is causing the Preview to fail, but one example of where a Preview isn't going to work is if you have any TM1 generated values embedded in the SQL statement. When the data is retrieved for the Preview, TM1 doesn't execute the Prolog, so can't perform the substitution as it doesn't know what these values are yet. Depending on the SQL, this could cause it to fail or return no records.

When the whole process is executed, TM1 runs the Prolog before the SQL is sent to the database, so any parameters will now have been instantiated and can be successfully substituted into the SQL.

It is quite common to specify a simplified query in the Query box to make sure that the correct Variables get defined, then assign the 'real' SQL, including any TM1 generated parameters, in the Prolog using DataSourceQuery.

As it looks like you may be doing more than a simple SELECT, one alternative is to push the code onto the server and call the Stored Procedure or equivalent in the Query box instead.

[jim wood]

Did you execute the SQL in the likes of Toad? Are you getting nothing as that is the result of your query?

Jim.

[OwlHelp]

Did you execute the SQL in the likes of Toad? Are you getting nothing as that is the result of your query?

Jim.

The original query works in MS SQL Server, I've included it for reference.

For TM1 I removed the comments, but I'm not clear which other parts would need to be changed.

Code: Select all

--Clearing temp table
IF OBJECT_ID('tempdb.dbo.#temptable', 'U') IS NOT NULL
  DROP TABLE #temptable;

--Union similar tables, selecting required fields

DECLARE @SQL nvarchar(max);

SELECT @SQL = COALESCE(@SQL + ' UNION ALL ' , '') + 'SELECT Field1, Field2, Field3 FROM [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME LIKE 'TablePrefix%';

--Storing results in temporary table

CREATE TABLE #temptable(
Field1, Field2, Field3);
INSERT INTO #temptable(Field1, Field2, Field3)
Exec(@SQL); 
select #temptable.Field1,#temptable.Field2,#temptable.Field3, table2.Field4 from #temptable

--Looking up values in temp table join another table

LEFT JOIN  table2 ON #temptable.Field1 = table2.Field4;

[OwlHelp]

You haven't said what you think it is that is causing the Preview to fail, but one example of where a Preview isn't going to work is if you have any TM1 generated values embedded in the SQL statement. When the data is retrieved for the Preview, TM1 doesn't execute the Prolog, so can't perform the substitution as it doesn't know what these values are yet. Depending on the SQL, this could cause it to fail or return no records.

When the whole process is executed, TM1 runs the Prolog before the SQL is sent to the database, so any parameters will now have been instantiated and can be successfully substituted into the SQL.

It is quite common to specify a simplified query in the Query box to make sure that the correct Variables get defined, then assign the 'real' SQL, including any TM1 generated parameters, in the Prolog using DataSourceQuery.

As it looks like you may be doing more than a simple SELECT, one alternative is to push the code onto the server and call the Stored Procedure or equivalent in the Query box instead.

I have now included the code in the previous comment. I think the "select [fields] where 1=0", then DataSourceQuery approach is the way to go here.

No TM1 generated parts in the query. As for why it doesn't work- I'm not sure. Maximum string length, declaring variables, create/insert/drop, and differences in how line breaks/spaces are treated are my top suspects.

[tomok]

As for why it doesn't work- I'm not sure. Maximum string length, declaring variables, create/insert/drop, and differences in how line breaks/spaces are treated are my top suspects.

I would put my money on exceeding the maximum string length allowed. Don't remember off the top of my head what it is because I never exceed it since I never issue a query this complex via TM1. I always create stored procedures whenever a query is going to be more than a simple SELECT FROM and then call that.

[jim wood]

Did you execute the SQL in the likes of Toad? Are you getting nothing as that is the result of your query?

Jim.

The original query works in MS SQL Server

I asked about the likes of Toad as it runs on ODBC also. When you run it in SQL it runs natively rather than via ODBC.