Query contains incompatible objects WIS 00008
I have two derived tables in a universe--they are derived so I can create a key to link to a date dimension and a time dimension. Both tables use this logic:
select to_number(TO_CHAR(Timestamp, 'yyyymmdd')) as date_key,
((Timestamp - TO_DATE(TO_CHAR(Timestamp, 'yyyymmdd'),'yyyymmdd')) * 86400) + 1 as time_key,
date_key links to date_key in d_date table. time_key links to time_key in d_time.
In Web Intelligence if I drag and drop a field from Table1 on the "SQL" button or "Run Query" it is fine, but if I drag a field from d_date or d_time I get the error "the query cannot run, because it contains incompatible objects. See you Business Objects adminstrator. (Error: WIS 00008).
"date_key" and "time_key" are datatype "NUMBER" in an Oracle database. The Universe connects using JDBC. I can run queries fine in a query tool using the derived tables linking to the dimensions.