Page 1 of 1
Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 6:14 am
by macsir
A strange question, in one TI, we are using ODBC to connect to one Oracle table to get data. For three particular columns, when I try to preview them in TI, they are just empty. (Actually, they have data in that table.) For previewing other columns, they are just fine.
E.g.
If I use SQL for these three particular columns in TI
Select A, B, C from T
In the preview window, they just don't show any value.
If I use SQL for other columns in TI
Select D,E from T
In the preview window, everything is normal and value is there.
If I use SQL to combine these three particular columns with others in TI
Select A, B, C,D, E from T
In the preview window, they just don't show any value also.
What is the possbile root cause here?
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 8:07 am
by Martin Ryan
Just guessing here, but it might be that they are ordering by column A and there are 10 null or blank rows in the table.
To test the theory try a query along the lines of select a, b, c from t where a<>'' and a is not null;
Martin
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 10:13 am
by Wim Gielis
Hello
Very recently, I encountered a similar issue. Certain columns (3 out of 7 or so) showed up as blank, with a purple background in the Preview window.
While the other columns where fine. TM1 presented a helpful popup that it could not interpret certain fields (loosely translated). Database is SQL Server.
I haven't spent too much time on it. It *might* be due to certain time functions (Year, Month, Day) that TM1 could not handle:
many (but not all) empty fields in the query deal with functions along these lines.
I solved it by adapting the query and retrieving the actual date. In the Data tab of the process, Subst() functions took out Day, Month, Year.
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 10:38 am
by asutcliffe
Wim Gielis wrote:Hello
Very recently, I encountered a similar issue. Certain columns (3 out of 7 or so) showed up as blank, with a purple background in the Preview window.
While the other columns where fine. TM1 presented a helpful popup that it could not interpret certain fields (loosely translated). Database is SQL Server.
I haven't spent too much time on it. It *might* be due to certain time functions (Year, Month, Day) that TM1 could not handle:
many (but not all) empty fields in the query deal with functions along these lines.
I solved it by adapting the query and retrieving the actual date. In the Data tab of the process, Subst() functions took out Day, Month, Year.
I encountered something similar a while back too - the columns were coloured in the preview window but I can't recall a popup message of any kind. I'm hazy on the details but I think I put it down to the ODBC driver not handling certain data types but I was able to work around it in a similar fashion to Wim. Are the columns that aren't working a different datatype to those that do work?
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 10:45 pm
by jstrygner
Just a shot in the dark.
Did you try if checking/unchecking the Unicode checkbox would change anything?
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 11:01 pm
by macsir
Hi, Thanks everyone. You remind me. Those three columns are all date columns in Oracle and they are all with timestamp field. It looks like TM1 doesn't support oracle date data type with timestamp well because another table only with date field can be shown properly. I resolve it by using to_char function to truncate the timestamp for those columns.
Re: Can't preview SQL data for some columns in TI?
Posted: Tue Feb 05, 2013 11:03 pm
by macsir
jstrygner wrote:Just a shot in the dark.
Did you try if checking/unchecking the Unicode checkbox would change anything?
Tried but doesn't help. See my latest post.
Re: Can't preview SQL data for some columns in TI?
Posted: Wed Feb 06, 2013 8:35 am
by rmackenzie
macsir wrote:Hi, Thanks everyone. You remind me. Those three columns are all date columns in Oracle and they are all with timestamp field. It looks like TM1 doesn't support oracle date data type with timestamp well because another table only with date field can be shown properly. I resolve it by using to_char function to truncate the timestamp for those columns.
There are data types in all database systems that the combination of TM1 and ODBC driver just can't translate. I'm not sure what the equivalent is for Oracle (GIYF) but this would show you all column types in a SQL server database - use this to be forewarned which columns are going to give you gip in your ODBC-based TI processes:
Code: Select all
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM [YOUR_DB_NAME_GOES_HERE_SOMETIMES].INFORMATION_SCHEMA.COLUMNS
Optionally, you might also use:
Code: Select all
WHERE DATA_TYPE = 'SOMETHING_THAT_TM1_CANT_HANDLE'
to filter out the troublesome columns.
More information here
for SQL Server and something similar
for Oracle.
Re: Can't preview SQL data for some columns in TI?
Posted: Wed Feb 13, 2013 12:57 am
by macsir
rmackenzie wrote:macsir wrote:Hi, Thanks everyone. You remind me. Those three columns are all date columns in Oracle and they are all with timestamp field. It looks like TM1 doesn't support oracle date data type with timestamp well because another table only with date field can be shown properly. I resolve it by using to_char function to truncate the timestamp for those columns.
There are data types in all database systems that the combination of TM1 and ODBC driver just can't translate. I'm not sure what the equivalent is for Oracle (GIYF) but this would show you all column types in a SQL server database - use this to be forewarned which columns are going to give you gip in your ODBC-based TI processes:
Code: Select all
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM [YOUR_DB_NAME_GOES_HERE_SOMETIMES].INFORMATION_SCHEMA.COLUMNS
Optionally, you might also use:
Code: Select all
WHERE DATA_TYPE = 'SOMETHING_THAT_TM1_CANT_HANDLE'
to filter out the troublesome columns.
More information here
for SQL Server and something similar
for Oracle.
Thanks for sharing. Goodo.