Hello,
I have 3 database tables where cardinality is as follows:
TAB A TAB B TAB C
1..1 --- 0..1 0..1 --- 1..1
Now this model is being developed on Cognos 7 catalog in Cognos 10.2 which is resulting in FULL OUTER JOIN which I think is because of Left Outer Join on Table B with Table A and RIGHT OUTER JOIN of Table B with TABLE C.
JOIN:
TAB C
left outer join
TAB B
on (TAB C.COL1 = TAB B.COL1)
full outer join
TAB A
on (TAB A.COL1 = TAB B.COL1)
In Cognos 7, these joins are defined as simple LEFT/RIGHT OUTER JOIN which I have considered as 1..1 --0..1 in Cognos 10.
How do we resolve above FULL OUTER JOIN not only in this case but in general?
Also the constraint is Tables are being used in Business Layer as single object with no aliases of it, what I mean is Table B is B in Business Layer with no other variant as B1 or B2.
Hope to find solution here.
Thanks
Hello, FULL OUTER JOIN is quite logical here because it can exist a couple of items from A and C without any relationship.
However, if you just make your query with un item B and un item A, it should be a LEFT/RIGHT OUTER JOIN instead of FULL OUTER JOIN.
;)
Hi hittony,
Sorry I didnt get what u said
Quote from: hittony on 31 Oct 2013 11:42:55 AM
However, if you just make your query with un item B and un item A, it should be a LEFT/RIGHT OUTER JOIN instead of FULL OUTER JOIN.
TAB C
left outer join
TAB B
on (TAB C.COL1 = TAB B.COL1)
full outer join
TAB A
on (TAB A.COL1 = TAB B.COL1)
or
TAB C
left outer join
TAB B
on (TAB C.COL1 = TAB B.COL1)
or
TAB B
right outer join
TAB A
on (TAB A.COL1 = TAB B.COL1)
As i understand you mean to say have alias of Tab B and join it separately with Tab A or C.
like this:
TAB A TAB B
1..1 --- 0..1
TAB B TAB C
0..1 --- 1..1
Quote from: rockky on 05 Nov 2013 10:51:55 AM
As i understand you mean to say have alias of Tab B and join it separately with Tab A or C.
like this:
TAB A TAB B
1..1 --- 0..1
TAB B TAB C
0..1 --- 1..1
That change nothing, the relationship between A and C is sitll a FULL OUTER JOIN. If it doesn't make any sense to you, you should try to prevent a query like that. For example, you can make
TAB A TAB C
1..1 --- 1..1and specify the result of such a quey between these 2 TABs shows only the pairs currently matched by COL1.
Hi
I don't get what is the solution for this then. Making inner join doesn't resolve it .
thanks
A native SQL can be a last resort