Joins

[ghostraider]

Hi All,

I have two query subjects 'Text' and 'Index' which have been imported from the database. 'Index' has three items 'Code', 'DHead', 'NHead'. The 'Index' subject has various Code values for the 'Code' item and each of these Codes along with the 'DHead' and 'Nhead' are further divided into model query subjects. For example:

Code1, Dhead, Nhead form one model query subject.
Code2, Dhead, Nhead form another model query subject.

These model query subjects are related to 'Text' with a cardinality of 1:n with 1 on the 'Text' side and n on the Model query subjects side. So these model query subjects are being treated as facts because of the n cardinality and when i query from all three subjects, i.e 'Text', 'Code1' and 'Code2' with a filter i am getting values which aren't related to the filter. I want only the items that satisfy my filter.

A clearer example is querying from Sales and Inventory facts with time dimension. I want only values for the items that have been sold and not unsold ones.

Can someone help me with this. Thank You.

[blom0344]

Look at the SQL generated. Cognos is probably trying the N--1--N situation with a stitch query. If this is the case you will see a split set of queries that are joined again through a full outer join by the Cognos server.
In that case using 1 filter on one of the N objects will not have the full effect..

[ghostraider]

Appreciate your response. That is exactly what is happening, a full outer join being generated. Is there an alternative to get this filter working? For the time being i applied a filter with the option 'After Auto Aggregation' and it seems to give me the results i want. In earlier case the filter was being applied to the one object alone but now the filter is applied to the whole resultant data.

[blom0344]

I spent the last two days on a report involving a stitch query and I have noticed the same behavior as you have.
I presume using 2 detail-filters (one for each N query subject) should also work.
The ' After auto-aggregation'  filter works because - I guess- it is applied AFTER the dataset is fetched from the database. This type of filter can only be executed by the Cognos server.

^{Actually, I spend more than 10 years working with Business Objects where no filter on aggregates was possible. So, I find this pretty nice stuff}