COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: technomorph on 07 Nov 2008 09:04:17 AM

Title: Maintain Outer Join
Post by: technomorph on 07 Nov 2008 09:04:17 AM
Hi,

I have two query subjects: product and material. They are joined using the following SQL logic.

select p.name, p.type, m.name, m.type
from product p
left join material m on p.prod_id = m.prod_id


In my model I also have a filter: Materials Not Deleted. When this is used in RS, the following syntax is generated:

select p.name, p.type, m.name, m.type
from product p
left join material m on p.prod_id = m.prod_id
where m.deleted_flag = 0


On the surface this may look correct, but for what I want, this syntax is actually incorrect because it effectively creates an inner join, and thus potentially omits data.

What I really want generated is:

select p.name, p.type, m.name, m.type
from product p
left join material m on p.prod_id = m.prod_id and m.deleted_flag = 0


Hopefully this makes sense. If not, I found the article below on the Cognos KB, which implies that the only way of achieving this is by hard-coding the filter in the join expression.

http://support.cognos.com/knowledgebase/googlesearch?load_kb_document=1&dr=kb1&uniqueid=56617

Any assistance gratefully received.

Thanks
Title: Re: Maintain Outer Join
Post by: blom0344 on 07 Nov 2008 09:43:16 AM
Cognos Filters will either change the where clause of the SQL ( detail filter) or be applied to filter the generated set (post-SQL by Cognos; summary filters).
They are not meant to change the joins that will be created.
I wonder if it would be a working solution to have the material table used twice in the model, the second time as an SQL object that has  m.deleted_flag = 0  ?
Title: Re: Maintain Outer Join
Post by: BlueSky on 07 Nov 2008 12:09:07 PM
Have you tried:

select p.name, p.type, m.name, m.type
from product p
left join material m on p.prod_id = m.prod_id
where m.deleted_flag = 0 OR m.deleted_flag is null

This should do the trick...
Title: Re: Maintain Outer Join
Post by: blom0344 on 10 Nov 2008 08:58:50 AM
Quote from: BlueSky on 07 Nov 2008 12:09:07 PM
Have you tried:

select p.name, p.type, m.name, m.type
from product p
left join material m on p.prod_id = m.prod_id
where m.deleted_flag = 0 OR m.deleted_flag is null

This should do the trick...

Yes, when you manually change the expression of the join in the framework. However, that will effect ALL further reports where this join comes into play..
Title: Re: Maintain Outer Join
Post by: technomorph on 17 Nov 2008 05:49:01 AM
Thanks to everyone for the suggestions.

The solution suggested by BlueSky worked, but there were a couple of issues. Firstly, performance was hammered. A report which previously took a couple of minutes to run, took up to half an hour. Secondly, the logic could only be applied locally to a specific filter, so could not account for scenarios where query items would be used dynamically in RS prompts.

In the end I resolved the problem by fudging the cardinality using dummy records in the dimension query subjects. It's not pretty, but it works.

Cheers