企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1251|回复: 8

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

[复制链接]

81

主题

389

帖子

575

积分

高级会员

Rank: 4

积分
575
QQ
发表于 2014-5-28 04:51:10 | 显示全部楼层 |阅读模式
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?
回复

使用道具 举报

74

主题

392

帖子

562

积分

高级会员

Rank: 4

积分
562
QQ
发表于 2014-5-28 06:00:58 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

76

主题

396

帖子

582

积分

高级会员

Rank: 4

积分
582
QQ
发表于 2014-5-28 06:29:16 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

57

主题

378

帖子

507

积分

高级会员

Rank: 4

积分
507
发表于 2014-5-28 06:29:36 | 显示全部楼层
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?
回复 支持 反对

使用道具 举报

70

主题

390

帖子

554

积分

高级会员

Rank: 4

积分
554
QQ
发表于 2014-5-28 07:09:14 | 显示全部楼层
Just a shot in the dark.
Did you try if checking/unchecking the Unicode checkbox would change anything?
回复 支持 反对

使用道具 举报

67

主题

407

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-5-28 07:16:52 | 显示全部楼层
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.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

64

主题

354

帖子

512

积分

高级会员

Rank: 4

积分
512
QQ
发表于 2014-5-28 07:22:30 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

81

主题

429

帖子

608

积分

高级会员

Rank: 4

积分
608
QQ
发表于 2014-5-28 09:00:30 | 显示全部楼层
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 TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM &#91;YOUR_DB_NAME_GOES_HERE_SOMETIMES&#93;.INFORMATION_SCHEMA.COLUMNS

Optionally, you might also use:Code: WHERE DATA_TYPE = 'SOMETHING_THAT_TM1_CANT_HANDLE' to filter out the troublesome columns.

More information here  and something similar .

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

67

主题

399

帖子

555

积分

高级会员

Rank: 4

积分
555
QQ
发表于 2014-5-28 09:19:32 | 显示全部楼层
rmackenzie wrote:[quote]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 TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM &#91;YOUR_DB_NAME_GOES_HERE_SOMETIMES&#93;.INFORMATION_SCHEMA.COLUMNS

Optionally, you might also use:Code: WHERE DATA_TYPE = 'SOMETHING_THAT_TM1_CANT_HANDLE' to filter out the troublesome columns.

More information here  and something similar .
[/quote]


Thanks for sharing. Goodo.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2022-9-30 11:55 , Processed in 0.058915 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表