Thanks very much, Anurag. Your solution put me on the right track to what I
needed. Sorry about the lack of a simplification and details - I'll provide
such in the future. FYI, the outer joins are required because I am building
a number of static Cognos Impromptu reports against the views. When the fact
table does not have a row for a certain combination of values, Impromptu
simply may not generate a row or column for a dimension combination that
would have been contained in that row. In order to ensure uniform formatting
throughout all reports, I needed all combinations of dimensions in the view.
Thanks again!
"Anurag Varma" <avdbi_at_hotmail.com> wrote in message
news:8ssU8.8780$68.275518_at_news4.srv.hcvlny.cv.net...
> I must admit that it took me a while to figure out what you might be
trying
> to do.
> Its always prudent to also provide a simplification of your problem (next
to
> the actual
> problem) .. and provide data specific details as to what you are trying to
> achieve.
>
> Essentially you are asking for a cartesian join between the dimension
tables
> and then want to join it (outer) with the fact table. You want to do this
to
> find out which combination does not exist in the fact table.
> If I am correct in my assumption, read on.
>
> I have simplified the problem ... but I guess you will get the idea ....
>
> avarma> select * from x1; -- Fact Table
>
> A B C
> ---------- ---------- ----------
> 1 19 1
> 2 20 1
>
> avarma> select * from f1; -- Dimension Table
>
> A A_DESC
> ---------- ----------
> 1 1_desc
> 2 2_desc
> 3 3_desc
>
> avarma> select * from f2; -- Dimension Table
>
> B B_DESC
> ---------- ----------
> 19 19_desc
> 20 20_desc
>
> avarma> select a_desc, b_desc, c from x1, f1, f2
> 2 where f1.a = x1.a(+) and f2.b = x1.b(+);
> where f1.a = x1.a(+) and f2.b = x1.b(+)
> *
> ERROR at line 2:
> ORA-01417: a table may be outer joined to at most one other table
>
> ^^^^^ You are trying this
>
> avarma> select a_desc, b_desc, nvl(c,0) from
> avarma> (select a, a_desc, b, b_desc from f1, f2) dimen, -- Do
> cartesian join for all combinations
> avarma> x1
> avarma> where dimen.a = x1.a(+) and dimen.b = x1.b(+);
>
> A_DESC B_DESC NVL(C,0)
> ---------- ---------- ----------
> 1_desc 19_desc 1
> 1_desc 20_desc 0
> 2_desc 19_desc 0
> 2_desc 20_desc 1
> 3_desc 19_desc 0
> 3_desc 20_desc 0
>
> 6 rows selected.
>
> Though it beats me as to why you would want to do this
> (unless of course, if I'm wrong in my assumptions) ... a cartesian join of
6
> dimension
> tables might be huge!
>
> Anurag
>
>
> "Gary Karpoff" <gary-k_at_vips.com> wrote in message
> news:aft03v$2g1$1_at_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 Wed Jul 10 2002 - 13:56:06 CDT
