Stumped in Framework Manager

[khdavis]

In FM 8.2, I have 6 tables in a database view. I've created my joins there also.

Tbl1 (Fact table)
Tbl2 (Fact table)
Tbl3 (Fact table)
Tbl4
Tbl5
Tbl6

Tbl1 joins to each of the other 5 tables.


When I create a business layer and try to add query items I get the following error:

BMT-MD-0003 UDA-SQL-0540 A "CASE or COALESCE" expression returning a blob result is not supported.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-263'.
UDA-SQL-0540 A "CASE or COALESCE" expression returning a blob result is not

Has anyone seen this error? Any suggestions on how to resolve?

I'm not joining on any BLOB fields or even using them in any calculations, however there are BLOB fields in all the fact tables.
Database is Oracle 10g.

 

[blom0344]

First thought:

Why are there BLOB's in your fact tables? Facts should store keys and facts and a BLOB would be neither..

Are you using database only processing or limited local processing?

Try using a cast_char conversion on the BLOB's

Ties Blom

 

[khdavis]

Yes there are BLOBs in the fact tables. Don't know why the database was set up that way.

I am using database only processing. I did try using limited local previously but that didn't resolve the issue.

I will try using the cast.

 

[AngelaCognos]

It's my understanding you should NEVER join a fact to a fact. If Table 1 is a fact and it's joined to all the tables, you have that. You want to end up with a star or snowflake where only dimension are joined to facts.

 

[blom0344]

Yes, star or snowflake (the last one to be reduced in the Cognos transformation layer), but depending on the datamodel that may be unattainable (especially with an OLTP schema)

Ties Blom