Full Outer Join Issue

[pramodb]

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

[hittony]

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.

;)

[pramodb]

Hi hittony,

Sorry I didnt get what u said

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.

[hittony]

Code Select Expand

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

Code Select Expand

TAB C
left outer join
TAB B
on (TAB C.COL1 = TAB B.COL1)

or

Code Select Expand

TAB B
right outer join
TAB A
on  (TAB A.COL1 = TAB B.COL1)

[pramodb]

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

[hittony]

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

Code Select Expand

TAB A                TAB C
         1..1 --- 1..1

and specify the result of such a quey between these 2 TABs shows only the pairs currently matched by COL1.

[pramodb]

Hi

I don't get what is the solution for this then. Making inner join doesn't resolve it .

thanks

[hittony]

A native SQL can be a last resort