Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Star Schema with Multiple Outer Joins - Help!

Star Schema with Multiple Outer Joins - Help!

From: Gary Karpoff <gary-k_at_vips.com>
Date: Tue, 2 Jul 2002 15:47:36 -0400
Message-ID: <aft03v$2g1$1@news.umbc.edu>


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 )

TMP2,
MSM_STATE SD,
MSM_PRD PD,
MSM_AGE_GRP AD,
MSM_RACE_ETHNCTY RD,
MSM_SEX XD

WHERE SD.STATE_ID = TMP2.STATE_ID (+)
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US