Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Star Schema with Multiple Outer Joins - Help!
How's this for a conundrum! I'm fairly new to Oracle and far from a SQL
expert and I will be forever grateful to any Oracle wizard out there who can
help with this challenge. My data model is a star schema with
MSM_TEST_STATE_SMRY_FACT as the fact table and 17 related dimension tables.
Each row in the fact table has the key values for all 17 dimensions
(STATE_ID, PRD_ID, AGE_GRP_ID, RACE_ETHNCTY_ID, SEX_ID, MAS_ID, BOE_ID,
SCHIP_ID, DUAL_ELGBL_ID, SRVC_CTGRY_ID, SRVC_TYPE_ID, CLM_TYPE_ID,
PGM_TYPE_ID, PLAN_TYPE_ID, MDCR_XOVER_ID, ADJSTMT_IND_ID, AND CNTY_ID) along
with various facts (UNIQ_ELGBLS_CNT, UNIQ_BENE_CNT, etc.). The only fact I
am concerned about is UNIQ_ELGBLS_CNT. The fact table rows consist of only
those combinations of dimensions that have related fact values (for example,
if the combination where all dimensions [STATE_ID through CNTY_ID] are equal
to 1 would result in a null value for UNIQ_ELGBLS_CNT, there is simply no
row containing this combination of dimension values in the fact table). I
need to create a view that contains every possible combination of 6
dimension values from the dimension tables along with corresponding values
for UNIQ_ELGBLS_CNT from the fact table. In those cases where the
combination of dimension IDs does not exist in the fact table, I need to
create a row containing the dimension values from the dimension tables along
with a zero for the UNIQ_ELGBLS_CNT fact.
So, in the SQL below, I try to select the needed ID values into the temp table TMP2 and outer join this table to the 5 appropriate dimension tables to create the view. Of course, this query produces the "you can only outer join a table to one other table" error. I've heard that there is a way around this but I haven't been able to generate the correct syntax. I need to create a number of similar views using various subsets of the data (or one monster view including everything), so any help will be MOST appreciated. Thanks!
CREATE VIEW MSM_STATE_SMRY_RPTS_QTRLY_V AS
SELECT STATE_ABRVTN_CD, FY_NUM, FSCL_QTR_NUM, AGE_GRP_CD_DESC,
RACE_ETHNCTY_CD_DESC,
SEX_CD_DESC, nvl(UNIQ_ELGBLS_CNT, 0)
FROM
(SELECT STATE_ID, PRD_ID, AGE_GRP_ID, RACE_ETHNCTY_ID, SEX_ID, UNIQ_ELGBLS_CNT FROM MSM_TEST_STATE_SMRY_FACT WHERE CNTY_ID IN (SELECT MSM_TEST_STATE_SMRY_FACT.CNTY_ID FROM MSM_TEST_STATE_SMRY_FACT, MSM_CNTY WHERE MSM_TEST_STATE_SMRY_FACT.CNTY_ID = MSM_CNTY.CNTY_ID AND MSM_CNTY.CNTY_CD = '***') AND MDCR_XOVR_ID = 3 AND CLM_TYPE_ID = 7 AND ADJSTMT_IND_ID = 7 AND MAS_ID = 8 AND BOE_ID = 12 AND SRVC_CTGRY_ID = 19 AND SRVC_TYPE_ID = 31 AND PGM_TYPE_ID = 10 AND PLAN_TYPE_ID = 12 AND DUAL_ELGBL_ID = 12 AND SCHIP_ID = 6 )
MSM_STATE SD, MSM_PRD PD, MSM_AGE_GRP AD, MSM_RACE_ETHNCTY RD, MSM_SEX XD
AND PD.PRD_ID = TMP2.PRD_ID (+) AND AD.AGE_GRP_ID = TMP2.AGE_GRP_ID (+) AND RD.RACE_ETHNCTY_ID = TMP2.RACE_ETHNCTY_ID (+) AND XD.SEX_ID = TMP2.SEX_ID (+)Received on Tue Jul 02 2002 - 14:47:36 CDT
![]() |
![]() |