Re: Simple Data warehouse question (cursor)

James wrote:
> 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
Is this part of a school assignment? I presume so as in a real data warehouse I can't imagine doing what you are doing.

No bulk binding
No FORALL
No APPEND hint

But to answer your question ... look at the options available with TO_CHAR at http://tahiti.oracle.com.

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