Creating a dimension

[eddoria01]

Using TM1 9.5.2

I am trying to create a dimension with route codes in it (using TI). The source data column contains more data than I need. I only need to add an element where it is like ST. How can this be set out in the SQL query on the data source tab?

Any help with this would be greatly appreciated.

Vera

[Alan Kirk]

Using TM1 9.5.2

I am trying to create a dimension with route codes in it (using TI). The source data column contains more data than I need. I only need to add an element where it is like ST. How can this be set out in the SQL query on the data source tab?

Any help with this would be greatly appreciated.

Vera

This is really more of an SQL question than a TM1 question, but in essence you'd just add a Where clause at the end of the SQL Statement:

Code: Select all

SELECT Field1, Field2
FROM Table1
WHERE Field1 Like "st*";

This is of course a very simple example and if the rest of the SQL is more complex it may depend on the DBMS (SQL Server, Access, Oracle, etc) that you're using.

[eddoria01]

Thanks for the reply.

I tried that in the TI screen but it does not like it. I want to use TM1 as the data is directly from our ERP system.

My code below returns zero records.
Select * from F0005
WHERE DRSY = '42' AND DRRT IN ('RT') AND DRKY LIKE ' ST*'

Is there a way in the TI metadata or data to bring back only ST* records?

Regards

Vera Hawkins

[tomok]

My code below returns zero records.
Select * from F0005
WHERE DRSY = '42' AND DRRT IN ('RT') AND DRKY LIKE ' ST*'

Is there a way in the TI metadata or data to bring back only ST* records?

That looks like a reasonable query but I have no idea if it's going to return records or not because i don't know your data. Have you tried running the query in the SQL server client to see if any records are returned? This is not a TM1 issue. You should figure out how to modify the query to get what you want and then plug that query into TI,

[Alan Kirk]

Thanks for the reply.

I tried that in the TI screen but it does not like it. I want to use TM1 as the data is directly from our ERP system.

My code below returns zero records.
Select * from F0005
WHERE DRSY = '42' AND DRRT IN ('RT') AND DRKY LIKE ' ST*'

Is there a way in the TI metadata or data to bring back only ST* records?

Regards

Vera Hawkins

Tomok beat me to the reply but one thing that I do note is that you have 7 spaces in front of your ST (it renders as a single pace unless you use the Code block when posting to the forum) so it'll only be returned if there are exactly 7 leading spaces before the ST.

One other thing {slaps hand to head}; just before that reply I was using Access, which does use an asterisk (*) as a wildcard. You haven't indicated which database type this is coming from but it's more common for the % character to be used as a wildcard. (It'd be ST% rather than ST* in a SQL Server database, for instance.) But Tomok is correct; get the query right in your database first, then just copy the SQL code to TI.

[eddoria01]

It worked in Access and I copied the SQL but did not work as TM1 SQL is different. The data is coming from an AS400.

Using % instead of * worked. Thanks for the tip.

Regards

Vera Hawkins

[Alan Kirk]

It worked in Access and I copied the SQL but did not work as TM1 SQL is different. The data is coming from an AS400.

Using % instead of * worked. Thanks for the tip.

You're welcome. One thing that you need to be aware of though; TM1 doesn't have an implementation of SQL. When you use an ODBC connection in TM1 you are simply connecting to a database which is being run by another application; Access, SQL Server, Oracle, MySQL, DB2 (if you must) or whatever. TurboIntegrator passes whatever code you put in the query window to that database, the database processes it and returns the relevant records. TI doesn't do any processing on the query at all; all of that is done on the database side. TI simply processes whatever data is fed to it as a result of the query.

For that reason the syntax that you need to use is generally worked out on the database first, because a lot of RDBMS's have their own syntactical quirks and (as you've found) an SQL query that works in one database may not work in another. TI won't know the difference; all it knows is what the text of the query to send to the database is, and what the records are that it gets back.