Oracle - Create a procedure to insert into a dimension table from multi tables

Question

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?

Answer 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;