URGENT SQL generating issue - join two tables on multiple columns PLEA

[dlutzker]

I have been grappling with a problem joining two tables that need to be joined on multiple columns. One metric is joining correctly (the second pass), but the first one insists on remaining joined on one field only. In general, how does the SQL engine decide when to join on multiple columns?

create table ZZMD002joins nologging as
select a12.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
	count(a12.EXAM_FACT_ID)  WJXBFS1
from SDW_ORDERED_EXAM_FACT_SS a12, 
	SDW_COMPLETER_EXAM_SEQ_SS a13
where a12.CONTACT_LU_ID = a13.CONTACT_LU_ID 
 and	a12.TAKEN_SEQUENCE = 1
group by a12.PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end 


create table ZZMD01 nologging as
select a11.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a12.TAKEN_EXAM_SEQUENCE, ',')=0 then a12.TAKEN_EXAM_SEQUENCE else substr(a12.TAKEN_EXAM_SEQUENCE, 1, instr(a12.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
 	count(distinct a11.COMPLETER_FACT_ID)  WJXBFS1
from SDW_COMPLETER_FACT a11, 
	SDW_COMPLETER_EXAM_SEQ_SS a12, 
	SDW_COMPLETER_END_TIME_DIM_VW a13
where a11.CONTACT_LU_ID = a12.CONTACT_LU_ID and 
	a11.PROGRAM_DIM_ID = a12.PROGRAM_DIM_ID and 
	a11.END_TIME_DIM_ID = a13.END_TIME_DIM_ID
 and	a13.YEAR_LU_ID in (54)
group by a11.PROGRAM_DIM_ID,
	case when instr(a12.TAKEN_EXAM_SEQUENCE, ',')=0 then a12.TAKEN_EXAM_SEQUENCE else substr(a12.TAKEN_EXAM_SEQUENCE, 1, instr(a12.TAKEN_EXAM_SEQUENCE, ',')-1) end

What I need the first pass to do is as follows:

create table ZZMD002joins nologging as
select a12.PROGRAM_DIM_ID  PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end  CustCol_22,
	count(a12.EXAM_FACT_ID)  WJXBFS1
from SDW_ORDERED_EXAM_FACT_SS a12, 
	SDW_COMPLETER_EXAM_SEQ_SS a13
where a12.CONTACT_LU_ID = a13.CONTACT_LU_ID 
[b]and a12.program_dim_id = a13.program_dim_id[/b]
 and	a12.TAKEN_SEQUENCE = 1
group by a12.PROGRAM_DIM_ID,
	case when instr(a13.TAKEN_EXAM_SEQUENCE, ',')=0 then a13.TAKEN_EXAM_SEQUENCE else substr(a13.TAKEN_EXAM_SEQUENCE, 1, instr(a13.TAKEN_EXAM_SEQUENCE, ',')-1) end

I have checked the results from this last SQL, and they are correct, but I can't get the MSTR engine to do it!!

Please, I have never posted an urgent request, but I am in need this time.
Thanks.
Dave

 

[dlutzker]

Well, a happy ending to this saga.

I opened a case with MSTR tech support, and they insisted that the logical table key calculation was functioning as intended, so I re-examined the "lowest level" of each attribute that was in the key, and those that should be in the key.

And herein lay the problem. I had a few attributes that I added for specific reports, that were in a 1-to-1 relationship to the true lowest level attributes, and I added them as children. Hence, I got a key with these "report-specific" attributes, and without their parents, which were the true lowest level objects.

So add that to the list of possible solutions to problems like this. I thankfully didn't need to rebuild any objects. Much as I would have like to blame the vendor and their corruptible metadata, the fault lay at home with me this time.

Better luck next time, eh?

Thanks to all once more. I am wrapping up this engagement, but maybe I'll run into you in a Cognos forum....or back here on the subsequent engagement!
Dave