Order of inner and outer joins

[misscognos]

Hello,

We have a catalog that was migrated from Impromptu to Cognos 10.

We have a structure like this:


T4
(0..1)
/
/
/
(1..1)
T1 (1..1)<--> (0..1) T2 (1..1) <--> (1..1) T3 |
(1..1)
(0..1)
T5


The problem is that there are inner and outer joins involved and therefore, the order in which the joins are done is important to get correct data.

In Impromptu, if we have a query that pulls data from T1, T3 and T4 get:

select [selection list]
from T1 Left outer join T2, [two left outer joins]
T3 Left outer join T4
where
T2.x=T3.y [inner join]



In Cognos 10 we get:

select [selection list]
from (
T1 Left outer join
(T2 inner join T3)
)
left outer join T4


Therefore, we get extra rows in Cognos 10 because the inner join is applied first. In Impromptu, the inner join is applied after the left outer joins and therefore some "filtering" is done by the inner join being applied later on to yield the desired results.


Two approaches to fix this I know of are:

1. I created a model query subject with T3, T4, T5. I created a second model query subject with T1 and T2. Then, I created an inner join between the two MODEL query subjects. This works. But, the part I don't like is that if I need a query with only something from T1, it will always include the left outer join to T2 in the sql even though it is not required for that query (behaves as a view).

2. Handle the joins in Report Studio.


Do you guys know of a better way to handle this?

 

[misscognos]

I tried to draw that but it got messed up.

This is what I meant to show:

T1 (1..1)....(0..1) t2.
T2 (1..1)....(1..1) t3.
T3 (1..1)... (0..1) t4.
T3 (1..1)....(0..1) t5.