0

I am designing a data warehouse for a small e-commerce, and I need to populate a dimension table for products from two tables product, and product_price_history. For simplicity let's say the columns for each table are as follow:

product (prod_id, brand) product_price_history (prod_id, price, min_pirce, start_date, end_date) product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date)

And I already have a sequence called prod_seq to generate keys for product_dimension table. This is what I have so far for my procedure, but I', getting errors:

create or replace 
procedure populate_product_dimension AS
BEGIN
INSERT into product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date)
SELECT seq_prod.nextval FROM DUAL
SELECT prod_id, brand
FROM product
SELECT price, min_pirce, start_date, end_date
FROM product_price_history
WHERE prod_id.product_pric_history = prod_id.product &
prod_id not in(select prod_id from product_dim);
Commit;
End populate_product_dimension;

I think the issue might be due to how I'm adding prod_key. Any one know a better way to do this?

SarekOfVulcan
  • 1,348
  • 4
  • 16
  • 35
seemvision
  • 242
  • 1
  • 2
  • 12

1 Answers1

1

SELECT query you use for INSERT. It has to be a JOIN and your syntax is wrong. It has to to be table_name/alias.column_name while referring the columns.

create or replace 
procedure populate_product_dimension AS
BEGIN
INSERT into product_dim (prod_key, prod_id, brand, price, min_pirce, start_date, end_date)
SELECT 
       seq_prod.nextval,
       p.prod_id,
       p.brand,
       ph.price,
       ph.min_pirce,
       ph.start_date,
       ph.end_date
FROM 
       product_price_history ph,
       product p
WHERE 
      ph.prod_id = p.prod_id
  AND ph.prod_id not in(select prod_id from product_dim);

Commit;
End populate_product_dimension;
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • Thank you sir. That did it! now let's say I want to add a status column to my dimension table, and if end_date in product_history_table is not set (null) it populate its value with Availble, and if not No Available. Is that possible in the code above? – seemvision Nov 16 '14 at 18:46
  • 1
    `NVL2(ph.end_date,'Available','Not Available')` would do it, for that – Maheswaran Ravisankar Nov 16 '14 at 18:58