Data Warehouse Cursor Problem

 I am trying to complete a piece of work for College but am having trouble with the completion of a cursor. The object of this small project is to create a very basic data warehouse from an operational system.

I have populated all of the dimension tables except one which is to be populated with the FACT table. These tables are to be populated with the cursor I am trying to complete.

I having difficulty understanding what the first select statement in the cursor does. For the region dimension table, I was asked to create a sequence to use as the primary key (region_id). The region_id in the operational table has different values e.g. 6000, 6001.

'dw_op' is the schema on the operation table which is accessed through the DB link 'q_link'.

Any thoughts on what is required to complete this cursor would be a big help.

Here is the incomplete anonymous block and cursor:

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...
...

s_value number;
s_cost number;

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

Insert into time values (s_time, s_day, s_month, s_year ... );

s_value := ... // how much it costs the company, unit_price * something? s_cost := ... // time something by the quantity

INSERT INTO sale VALUES (ord.client_id, order_line.product_id, ord.SALES_REP_ID, r_id, s_time, order_line.quantity, s_value, s_cost); // need to find out how to enter select info into table

end loop
end; Received on Wed Apr 21 2004 - 05:58:48 CDT