Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    England
    Posts
    15

    Unanswered: Simple Data warehouse question (cursor)

    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

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Code:
    CURSOR c_time is
       SELECT order_date FROM dw_op.ord@q_link;
    ...
    FOR c_rec2 in c_time LOOP
       s_month := c_rec2.order_date;
    END LOOP;
    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:

    Code:
    CURSOR c_time (myDate DATE) IS
       SELECT order_date FROM dw_op.ord@q_link
       WHERE order_date = myDate;
    ...
    FOR c_rec2 IN c_time( compareDate)  LOOP
       s_month := c_rec2.order_date;
    END LOOP;
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •