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
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.
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..
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