Re: Data Warehouse Cursor Problem

James wrote:

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

Without seeing the full schema and the full specification any helpful hint might be worse than wrong.

What I would like to see, as an instructor, is formatting. put your keywords like SELECT, FROM, WHERE, ORDER BY in caps, be consistent with you use of case ... for example why is SALES_REP_ID capitalized? And 'FROM' should be on the left margin.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)