Maintain Outer Join

[technomorph]

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

[blom0344]

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  ?

[BlueSky]

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...

[blom0344]

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..

[technomorph]

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