Errors caused adding foreign key table

[erics]

Hi there,
I'm working Cognos 10.1.1
I have an issue where a number of our products have changed their names. I don't want to change the name in the DB because business rules are often applied by the product names.
So, I created a translation matrix of sorts with the old name and the new one. I figured that I could use a replace so that the sales history would show the updated product names.

I've brought the matrix into the framework manager, and created a relationship between the two. When I run a test, there's no problem that I can see.
Next, I create a data item. Use the replace function to replace the old product name with a new one. When I test it in the data item, it works well. However, when I test the query subject as a whole, I get an error RQP-DEF-0105  relating to a completely different relationship.

Has anyone seen this error before? Can we use the replace function in the query studio? If not, what do you use?

Eric

[erics]

After more investigation, the issue is not the replace.
It seems like the query subject doesn't work if there are relations to more than one table.
Unfortunately, query information does not show me the SQL for this ...

[MFGF]

Hi there,
I'm working Cognos 10.1.1
I have an issue where a number of our products have changed their names. I don't want to change the name in the DB because business rules are often applied by the product names.
So, I created a translation matrix of sorts with the old name and the new one. I figured that I could use a replace so that the sales history would show the updated product names.

I've brought the matrix into the framework manager, and created a relationship between the two. When I run a test, there's no problem that I can see.
Next, I create a data item. Use the replace function to replace the old product name with a new one. When I test it in the data item, it works well. However, when I test the query subject as a whole, I get an error RQP-DEF-0105  relating to a completely different relationship.

Has anyone seen this error before? Can we use the replace function in the query studio? If not, what do you use?

Eric

Hi,

It's very difficult to say what the issue might be without seeing your model or knowing more about the query subjects and the relationships. The error message you posted probably has a long version that looks similar to:

RQP-DEF-0105;The query uses the join xxxxx between entities vvvvv and zzzzz. This join is unacceptable since its expression does not reference the entities of the join.

What objects are you referencing when you test? What do they link to? What are the underlying relationships?

MF.

[erics]

I can probably include an image, but I'll have to take the table names and fields out.

Essentially, what we have is a sales table, and 3 tables joined to it.
Ones for salesppl, it's a 1.1 to 1.n (for the sales table).
The second is region descriptions, also a 1.1 to 1.n (for the sales table).
The third which I just added is the product name matrix, which's a a 0.1 to 0.n (for the sales table).

It looks like Cognos is objecting to the first relationship. The error that I'm getting is:

BMT-MD-0003 RQP-DEF-0105 The query uses the join [ vw_Sales <--> tbl_Salesperson ] between entities vw_Sales and tbl_Salesperson. This join is unacceptable since its expression does not reference the entities of the join.

I'm a bit confused by the error message itself. The vw_sales is a transitive entity, so it doesn't makes sense that the salesperson and the product matrix be joined.

[erics]

It's odd, I tweaked the cardinality, and now I only get the error when I refer to fields in the product matrix.
I'm confused because the cardinality of the table 1.1 or 1.n, so it can't set up a situation where there are no results and cause errors.

[navissar]

How's your join built?

[erics]

For the Salesppl table, and the region description table, they're inner joins.
For the matrix table that I'm adding it's a left outer join. So, all sales records, and matrix records as they're available.

I took the SQL, and it runs fine, so I'm not sure why I'm getting this error.
Also, when I create the relationship, I don't get the error. It comes about when I try adding a field from the matrix table into the view, which already contains elements from numerous tables.

[erics]

Just on a hunch, I created a new entity with the same values, and it passes validation properly.
Looks like it's one of those Cognos glitches that cost you time and hair.
Going to delete entity and recreate it now.

Thanks!
Eric