Can't preview SQL data for some columns in TI?

[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?

[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

[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.

[asutcliffe]

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?

[jstrygner]

Just a shot in the dark.
Did you try if checking/unchecking the Unicode checkbox would change anything?

[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.

[macsir]

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.

[rmackenzie]

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.

[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.

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.