Hello, IRM Stakeholders -

In our last meeting, we discussed a ticket we currently have open with IBM for an issue a user reported with outer joins. It looks like Cognos is processing these SQL Statements as Select statements rather than Select distinct, meaning that the values from one of the base queries is not returning the expected results in the joined query.

So far, we have only found this issue in joins where there is no measure or aggregation in the result set, where the underlying data has multiple records per join key (e.g., multiple rows per Person ID where the queries are joined on Person ID), and there are multiple join criteria.

We have found several workarounds.

1. Change the Use local cache Query property on the joined query to No (see attached document for screenshot).
2.  Add a measure or add an aggregation on any of the items from the base queries to the final query.
3. Change the property on at least one filter in each base query to After Auto Aggregation.
4. Turn the Auto group & summarize property on the query after an outer join to No – but it looks like this only works for one level of join.
5. Write case statements for all items brought in (case when x is null then ‘’ else x).
6. Use a base/master query in order to force the aggregate of the rows (see attached document for screenshot).

Please contact us at irm@cu.edu if you have any questions.

Thank you!

Aron Sage

Business Intelligence Analyst

University Information Services

University of Colorado

1800 Grant Street, Suite 317A

Denver, CO 80203

t 303 860 6216

aron.sage@cu.edu