Outer Join on attributes 1

[138006]

Hi ,
I have 2 tables of which 1 is a dimension table. Another table is joined with it but its not a pure snowflake (our structure is not a pure snowflake/star--there is a lot of normalizations in the db structure). Now I want an outer join on one column between these 2 tables.

How to achieve it?

Thanks

 

[JaC74]

How is this different from any other outer join? Do you first pass and left outer (or Right or full, which ever floats your boat) to the second pass on the one column that you want.

 

[138006]

Its not about Outer join between passes. In the same pass and between 2 different tables.

 

[JaC74]

Do you mean something like this?

Select Attr1, Attr2, Metr1
From FactTable1 F
Left Outer Join
DimTable1 D
on F.Attr1 = D.Attr1
Where ....

If so, I am not sure if you can specify join type in the From section.

 

[138006]

No,
The SQL will be something like:

Select
D1.a1,
D2.a2,
sum(F1.f1)
from D1, D2, F1
where
F1.a1 = D1.a1
and D1.a2 = D2.a2 (+)

D1 is a Dimension, F1 is a fact. D2 is joined to D1.

Thanks

 

[entaroadun]

MSTR cannot handle outer join relationships between attributes.

MSTR can handle this, however:

Select
D1.a1,
D2.a2,
sum(F1.f1)
from D1, D2, F1
where
F1.a1 = D1.a1 (+)
and D1.a2 = D2.a2 (+)

 

[138006]

I don't want the Outer Join betweeen the Fact and Dimension table.
But between 2 other tables as mentioned.

Isn't there any way that the Outer Join is achieved?

Thanks

 

[entaroadun]

No. You need to redesign your D2 dim table so that the outer join is not necessary. Many data warehousing references exist online that explain how to do this.

 

[Z3]

Or use an outer join view, or some other database tricks. But you should still clean up/filter out the data.

 

[138006]

Hi
What is an Outer Join view? Database level changes is difficult for us to implement.
Is there any MSTR work arounds??

Thanks

 

[Z3]

A view with the correct outerjoin definition in it. e.g.

create or replace view as
select a11.*, a12.*
from table1 a11
left outer join table2 a12 on (a11.col1=a12.col2)

I saw many places had the same problem. The places I am working for is one of them. I have freedom to create tables, but I don't have right to create VIEWS. What I did was, I threaten the DBA that, if I don't get my view, I will create the table in my pre SQL. And I can create it anytime I run this report, and he has to tune it . At the end, I got my view, quickly.

 

[entaroadun]

Threatening the DBA... excellent tactic. You're lucky, though. At least you're on a project where the DBA is responsible for performance tuning.