I am working on a simple data warehouse(for College) and at this point am trying to populate 2 data warehouse tables using an anonymous block.

One table is the FACT table (Sales) and the other is a dimension table (Time). I am currently having a bit of a problem populating the month field of the time table. The value is coming from the order_date of the Ord table in the operational database.

However, the loop currently in the cursor inserts the same date into time table with a primary key incremented by one. I have tried placing the INSERT into Time statement inside the second loop but it repeatedly inserts the same data.

Here is my anonymous block (with table structures underneath), any help would be great:

DECLARE
CURSOR c_sales is
SELECT order_line.product_id, order_line.quantity, product.unit_cost, product.unit_price, ord.client_id, ord.sales_rep_id, ord.order_date
FROM dw_op.ord@q_link, dw_op.order_line@q_link, dw_op.product@q_link
WHERE ord.order_id = order_line.order_id AND order_line.product_id = product.product_id;
CURSOR c_time is
SELECT order_date FROM dw_op.ord@q_link;
r_id number;
s_time varchar2(10);
s_month VARCHAR2(10);

client_id_var number(4);
product_id_var number(6);
quantity_var number(6);
SR_ID_var number(6);
s_value number(8);
s_cost number(8);

BEGIN
FOR c_rec in c_sales LOOP
SELECT region_id into r_id
FROM region where region_name =
(SELECT region_name FROM dw_op.sales_region@q_link,
dw_op.sales_rep@q_link WHERE sales_region.region_id =
sales_rep.region_id AND SR_id = c_rec.SALES_REP_ID);


SELECT time_seq.nextval INTO s_time FROM dual;
FOR c_rec2 in c_time LOOP
s_month := c_rec2.order_date;
END LOOP;
INSERT INTO TIME (TIMECODE, MONTH) VALUES (s_time, s_month);

client_id_var := c_rec.client_id;
product_id_var := c_rec.product_id;
quantity_var := c_rec.quantity;
SR_ID_var := c_rec.sales_rep_id;
s_value := c_rec.unit_price * c_rec.quantity;
s_cost := c_rec.unit_cost * c_rec.quantity;

INSERT INTO sale (client_type_id, product_id, SR_ID, Region_id, TimeCode, Quantity, SALEVALUE, COSTVALUE) VALUES (client_id_var, product_id_var, SR_ID_var, r_id, s_time, quantity_var, s_value, s_cost);
END LOOP;
END;

Table structures:

SALE table:
Name
--------------
CLIENT_TYPE_ID
PRODUCT_ID
SR_ID
REGION_ID
TIMECODE
QUANTITY
SALEVALUE
COSTVALUE

TIME table:
Name
--------
TIMECODE
DAY
MONTH
YEAR

You need to re-write this cursor definition... most likely you'll have to put a parameter on the cursor definition. What is happening is that the cursor (FOR ... IN ... LOOP) goes completely through the table defined and is returning the last date. In other words, a cursor exits (and returns your value) when it finishes executing your query, which returns all rows in the ORD table.

Change like this:

This will force a lookup to occur with "each" call to the cursor and execute the cursor loop for the specific order_date.

Hope this helps.

JoeB