Simple Data warehouse question (cursor)

From: James <nospamheredude_at_yahoo.com>
Date: Sat, 24 Apr 2004 15:41:28 +0000 (UTC)
Message-ID: <c6e1r8$leo$1@sparta.btinternet.com>

I am working on a simple data warehouse 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_at_q_link, dw_op.order_line_at_q_link, dw_op.product_at_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_at_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_at_q_link,
dw_op.sales_rep_at_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 Received on Sat Apr 24 2004 - 10:41:28 CDT