| Table Join [message #400060] |
Sat, 25 April 2009 10:31  |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Hi,
I wish to inquire how i can achieve the following
I have a fact table which has multiple lookup fields (close to 30) to dimension tables. I wish to populate the id's from the dimension table. I can achive as follows
select a.col1, a.col2, a.col3, a.col4, a.col5 ..
from fact a, dim1 b, dim2 c, dim3 d, dim4 e ..
where
a.col1 = b.col1(+)
a.col2 = c.col1(+)
..
How can i make change to the above to set the id's to -1 if a corresponding entry is not found in the dimension table.
Any advice is very appreaciated.
|
|
|
|
|
|
| Re: Table Join [message #400081 is a reply to message #400064] |
Sat, 25 April 2009 20:35  |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Hi Michel,
I agree with you. But there is a problem with the approach,, as below. Lets say we have only one dimension table
select nvl(b.col1,-1), a.col2, a.col3
from a source, b dimension
where a.col1 = b.col1(+)
There may be some records in source table that does not have corresponding key in dimension table. So i wish to assign -l if the lookup key is null or not found.
Using nvl, i can only take care of nulls.
|
|
|
|
|
|
| Re: Table Join [message #400084 is a reply to message #400082] |
Sat, 25 April 2009 21:34 |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Tq, actually the nvl solves the issue based on example below. Im not sure why i was not getting it right the first time.
create table source_sales(
product_code varchar2(10),
date_sale date,
quantity number);
insert into source_sales values ('A','1-Jan-2009',20);
insert into source_sales values ('A','1-Jan-2009',10);
insert into source_sales values ('B','2-Jan-2009',30);
insert into source_sales values ('C','3-Jan-2009',20);
create table fact_sales(
product_id number,
date_id number,
quantity number );
create table date_dimension(
date_id number,
date_sale date);
insert into date_dimension values (1,'1-Jan-2009');
insert into date_dimension values (2,'2-Jan-2009');
create table product_dimension(
product_id number,
product_code varchar2(10));
INSERT INTO PRODUCT_DIMENSION values (1,'A');
INSERT INTO PRODUCT_DIMENSION values (2,'C');
commit;
Query as below returned the expected results
select nvl(b.product_id,'999'), nvl(c.date_id,'999') , a.quantity
from source_sales a, product_dimension b, date_dimension c
where a.product_code = b.product_code(+)
and a.date_sale = c.date_sale (+)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
