Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    USA
    Posts
    4

    Unhappy Unanswered: pls/sql help needed urgent.

    database:Oracle 7.x
    codingl/sql
    table_size:125 million rows
    table_name: ORDER_PROC

    columns:
    Name Null? Type
    ------------------------------- -------- ----
    CUST_ID NOT NULL NUMBER(10)
    DIV_CODE NOT NULL CHAR(2)
    ORDER_ID NOT NULL CHAR(8)
    LINE_NBR NOT NULL NUMBER(4)
    TOTAL_DEMAND_AMT NUMBER(9,2)
    ORDER_DISC_AMT NUMBER(9,2)
    DISC_AMT NUMBER(9,2)
    ORDER_DEMAND_AMT NUMBER(9,2)
    INSERT_DATE DATE

    Requirement: Update disc_amt
    Formula: disc_amt=(total_demand_amt/order_demand_amt)*order_disc_amt
    Condition: Order_disc_amt > 0

    Read a summary of order_proc information into a cursor (summarize at cust_id, div_code, order_id level) Summarize total demand amt (line_nbr) and order_disc_amt (order_id). This summary data is required in updating.

    I am Unix & SQL guy. I am not a pl/sql coding person. I know very little about cursors. struggled a week with my limited knowledge, now asking for help from a pl/sql expert. Any questions...? Thanking you in advance.
    Last edited by hnvhelp; 01-13-03 at 15:01.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pls/sql help needed urgent.

    Originally posted by hnvhelp
    database:Oracle 7.x
    codingl/sql
    table_size:125 million rows

    columns:
    Name Null? Type
    ------------------------------- -------- ----
    CUST_ID NOT NULL NUMBER(10)
    DIV_CODE NOT NULL CHAR(2)
    ORDER_ID NOT NULL CHAR(8)
    LINE_NBR NOT NULL NUMBER(4)
    TOTAL_DEMAND_AMT NUMBER(9,2)
    ORDER_DISC_AMT NUMBER(9,2)
    DISC_AMT NUMBER(9,2)
    ORDER_DEMAND_AMT NUMBER(9,2)
    INSERT_DATE DATE

    Requirement: Update disc_amt
    Formula: disc_amt=(total_demand_amt/order_demand_amt)*order_disc_amt
    Condition: Order_disc_amt > 0

    Summarize total_demand_amt (at line_nbr level) and order_disc_amt (at order_id level) grouped by cust_id, div_code & order_id.

    I am Unix & SQL guy. I am not a pl/sql coding person. I know very little about cursors. struggled a week with my limited knowledge, now asking for help from a pl/sql expert. Any questions...? Thanking you in advance.
    It is not clear to me whether the "Summarize..." sentence relates to the update, or is a separate requirement.

    Assuming it is a separate requirement, the update would be simply:

    UPDATE tablename
    SET disc_amt=(total_demand_amt/order_demand_amt) * order_disc_amt
    WHERE Order_disc_amt > 0;

    I'm not sure I understand the next bit. Could be:

    SELECT cust_id, div_code, order_id, SUM(total_demand_amt), SUM(order_disc_amt)
    FROM tablename
    GROUP BY cust_id, div_code, order_id;

    ... but that may not be quite right.

  3. #3
    Join Date
    Jan 2003
    Location
    USA
    Posts
    4
    to andrewst,

    Yes, Summarize relates to the update statement. First, read this summary of data into a cursor (inluding cust_id, div_code, order_id) and then update the corresponding disc_amt whose order_disc_amt > 0.



    another different question:
    And even if it is a direct update, can we update a table simply using a update statement who has 125+ million rows....?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by hnvhelp
    to andrewst,

    Yes, Summarize relates to the update statement. First, read this summary of data into a cursor (inluding cust_id, div_code, order_id) and then update the corresponding disc_amt whose order_disc_amt > 0.



    another different question:
    And even if it is a direct update, can we update a table simply using a update statement who has 125+ million rows....?
    I can't really follow the requirements for this update. Maybe you could post an example with data before and after?

    Yes, you can update a 125+ million row table with an UPDATE statement. Depending on what percentage of rows in the table the UPDATE will modify, you may need a large rollback segment, but using a single UPDATE statement is much more efficient than using PL/SQL cursors and loops.

Posting Permissions

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