Home » SQL & PL/SQL » SQL & PL/SQL » Table Join (10g)
Table Join [message #400060] Sat, 25 April 2009 10:31 Go to next message
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 #400064 is a reply to message #400060] Sat, 25 April 2009 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use NVL or DECODE/CASE depending on the actual query and tables.

Regards
Michel
Re: Table Join [message #400081 is a reply to message #400064] Sat, 25 April 2009 20:35 Go to previous messageGo to next message
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 #400082 is a reply to message #400060] Sat, 25 April 2009 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Table Join [message #400084 is a reply to message #400082] Sat, 25 April 2009 21:34 Go to previous messageGo to next message
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 (+)



Re: Table Join [message #400089 is a reply to message #400060] Sat, 25 April 2009 22:03 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Query as below returned the expected results
Congratulations on solving your problem.
Previous Topic: Homeowrk Help: Joining Multiple Tables
Next Topic: Default null parameters in stored procedure
Goto Forum:
  


Current Time: Sat Dec 03 05:57:44 CST 2016

Total time taken to generate the page: 0.09784 seconds