Cognos 8.4 DB2 Stitch query

[jmsellner]

I have three DB2 join together based on the following

Table A joins to Table B based on Case No and Service Period 1.N to 1.1
Table B joins to Table C based on case No and Service Periond 1.1 to 0.N

Table A has PK of Case, Service Period, Case Owner, also FK of Case, Service Period
Table B has PK of Case, Service Period
Table C has PK of Case, Service Period, Care Plan No, also FK of Case, Service Period


When I try to query from Table A and B, the query is correct.
When I try to query from Table B and C, the query is correct.
When I try to query from Table A to C, I get a stitch query based on Case No.

I have played around with the determinants and I can't get it to work.
- Tried just the unique determine based on the pK that cognos set up at import
-  Tried the unique determine based on the PK, and added seperate determinant group by for Case and service period
-  Tried the unique determine based on the PK, and added One determinant group by for Case and service period
- tried removing the determinants completely

Every which way I try, I get a rsum(1 order by A.Case asc local) as sc and rsum(1 order by C.Case asc local) as sc with a
D6
        full outer join
       D7
        on (D6.sc = D7.sc)

How do I correct this so it will join base on A.Case=C.Case and A.Service_period = C.Service_Period

[DustPanMan]

You could always create that direct join between Table A and C.  The problem with how it is set up now is you have a one to many from A to B and then a non to many from B to C.  This will create a full outer join every time.  If you change your B to C relationship to be 1.1 to 1.N, you may be able to get a left outer or right outer.  I would just create the relationship between A and C though.

[blom0344]

If both A and C contain facts then you simply cannot directly join them. This would introduce overcounting/oversummarizing since the combination of Case and Service period is non-unique for the facts.
Remember that the stitch is meant to take care of correctly aggregating 2 sets and applying the 'stitch' (= full outer join + coalesce) afterwards. This is the only way (apart from using seperate queries in the report) to have a correct multi-fact solution.

If no facts exists ,than it may be worthwhile to use 1:1 cardinality and check the resulting query..

[BIsrik]

If A and C contains fact at different level of granularity then stiched query needs to used. If you have the fact at the same granularity then u can change the granularity to 1:1

Srik