TM1 SQL

[appleglaze28]

Is all applicable SQL query statement or script applicable for TI? Since I'm currently using TOAD but when I sometimes use working SQL script on TI it doesnt show any values but on TOAD it does?

[Alan Kirk]

Is all applicable SQL query statement or script applicable for TI? Since I'm currently using TOAD but when I sometimes use working SQL script on TI it doesnt show any values but on TOAD it does?

Ah, TOAD... I remember TOAD. Haven't used it for years, but then I haven't used an Oracle database for years.

See the following thread:
http://forums.olapforums.com/viewtopic. ... 957&p=5406

In short, TI doesn't do anything with SQL statements aside from pass them to the database. It doesn't interpret them, it doesn't process them. It simply says to the DBMS "here's the query, now give me the rows". If you're getting rows returned in TOAD but not in TI, and you're using EXACTLY the same syntax (an important qualification, particularly if parameters are involved), then my suspicion is that the issue is with your ODBC connection to the Oracle database, not with TI as such.

You should be absolutely certain that you're using the same login and password in both environments. If you're using different ones, it's possible that the login that you're using on TOAD has permissions to tables or views that the login that you're using for TI's ODBC connection doesn't.

Also check that the ODBC data driver is current; as I recall, ODBC connections to Oracle databases are considerably more "fussy" than connections to Access, SQL Server, etc.

[Michel Zijlema]

Hi,

As Alan mentioned TI just passes the query to the database and retrieves the resultset. Some things to note: if (in the query window on the datasource tab) your SQL consists of multiple, semicolon separated, statements, I think TI will only pass the first one. Another thing: TI doesn't like double quotes - if you have literals in your query statement make sure that you use single quotes instead of double quotes.

Michel

[appleglaze28]

I have some instances that the SQL I use in TOAD and TI are the same and I get a query result but at times there are query result but non for TI.

Sometimes I even use Native SQL produce by the Report Studio on TOAD, sometimes I have to re-edit it removing the table name or something in TI. Some work some doesn't

[Alan Kirk]

I have some instances that the SQL I use in TOAD and TI are the same and I get a query result but at times there are query result but non for TI.

Sometimes I even use Native SQL produce by the Report Studio on TOAD, sometimes I have to re-edit it removing the table name or something in TI. Some work some doesn't

It's still not TI that's causing the issue unless it's one of the problems that Michel mentioned. TI is nothing more than a string passer, and data receiver.

Assuming that it isn't one of the formatting issues that Michel mentioned isn't at play, I'll bet that you'd get exactly the same results as TI if you ran the query via MS Query as well. Which would again bring it back to login access and / or ODBC driver.