Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Unanswered: update alternate 2 records with repeating values

    Hi,

    I am using oracle 10g.

    I have 2 tables
    1.
    costomer_complaints table
    customer_id complaint_id engineer_id
    1001 5001 15
    1002 5002 15
    1003 5003 15
    1004 5004 15
    1005 5005 15
    1006 5006 15
    1007 5007 15
    1008 5008 15
    1009 5009 15
    1010 5010 15

    2.
    SELECT jr_engineer_id FROM TIM_emp where engineer_id=15;
    20
    21

    Required output:
    I want to update customer_complaints table as described below.
    customer_id complaint_id engineer_id
    1001 5001 20
    1002 5002 20
    1003 5003 21
    1004 5004 21
    1005 5005 20
    1006 5006 20
    1007 5007 21
    1008 5008 21
    1009 5009 20
    1010 5010 20

    I tried to write cursor within a cursor to get it implemented, but it is not working.
    Can you please help me with this?

    --P J S

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The sample data was too regularly.

    For example:
    (1) Both of customer_id and complaint_id were consecuive.
    (2) customer_id and complaint_id were sequenced same.

    (Q1) Were (1) and (2) true?

    (Q1-1) If (2) was not true, which sequence do you want to take?


    (Q2)
    update alternate 2 records with repeating values
    Are always the number of result rows of the query
    SELECT jr_engineer_id FROM TIM_emp where engineer_id=nn;
    was 2?

  3. #3
    Join Date
    Jul 2013
    Posts
    24
    Thanks for your reply
    (1),(2) are not true.
    I just want to replace engineer_id with its child records ids.

    Can it be done using nested cursors?
    If yes, Which cursor should be inner and which could be outer in this case?

    Thanks
    --Priyadarshini

  4. #4
    Join Date
    Jul 2013
    Posts
    24
    Yes. number of result rows of the query is always 2

    --P J S

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by P J S View Post
    Yes. number of result rows of the query is always 2

    --P J S
    Show us your code.
    Or are you waiting for someone to do the work for you?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    And Tonkuma was trying to ask how do you decide which rows get jr_engineer 20 and which ones get 21? The data is too generic for us to determine when it should be one or the other. Seems like you are just wanting the first 2 to be updated to 20 then the next 2 to 21 and then start over again?

  7. #7
    Join Date
    Jul 2013
    Posts
    24
    Hi,

    Happy new Year.
    Hope you all are doing fine.

    Yes. I just want to update 20 for first to complaints. 21 to second two complaints, again 20 for 3rd two complaints and so on. Here we know that there are only 2 Jr engineers 20 and 21.

    I have written below code for above requirement and it is now running successfully. This was the requirement given.

    How ever now I am trying do it for unknown number of Jr_engineer records, instead of 2.

    BEGIN
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1001, 5001, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1002, 5002, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1003, 5003, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1004, 5004, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1005, 5005, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1006, 5006, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1007, 5007, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1008, 5008, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1009, 5009, 15);
    INSERT INTO customer_complaints(customer_id, complaint_id, engineer_id ) VALUES(1010, 5010, 15);

    INSERT INTO TIM_EMP(jr_engineer_id,engineer_id) VALUES(20,15);
    INSERT INTO TIM_EMP(jr_engineer_id,engineer_id) VALUES(21,15);
    END;


    DECLARE
    CURSOR cGetComplaints IS
    SELECT * FROM customer_complaints;

    CURSOR cGetJr_Engr IS
    SELECT jr_engineer_id,rownum pos FROM TIM_emp where engineer_id=15;


    rrow cGetComplaints%ROWTYPE;
    cnt1 NUMBER := 1;
    flag NUMBER := 0;

    BEGIN

    FOR vGetComplaints IN cGetComplaints LOOP
    FOR vGetJr_Engr IN cGetJr_Engr LOOP

    IF cnt1 <= 2 AND vGetJr_Engr.pos = 1 AND flag = 0 THEN
    update customer_complaints r
    set r.engineer_id = vGetJr_Engr.jr_engineer_id
    where r.customer_id = vGetComplaints .customer_id;
    cnt1 := cnt1 + 1;
    flag := 1;
    ELSIF cnt1 <= 2 AND vGetJr_Engr.pos <> 1 THEN
    null;
    ELSIF cnt1 <= 4 AND vGetJr_Engr.pos = 2 AND flag = 0 THEN
    update customer_complaints r
    set r.engineer_id = vGetJr_Engr.jr_engineer_id
    where r.customer_id = vGetComplaints .customer_id;
    cnt1 := cnt1 + 1;
    flag := 1;
    ELSIF cnt1 <= 4 AND vGetJr_Engr.pos <> 2 THEN
    null;
    END IF;

    IF cnt1 = 5 THEN
    cnt1 := 1;
    END IF;

    END LOOP;
    flag := 0;
    END LOOP;

    END;


    How ever now I am trying do it for unknown number of Jr_engineer records, instead of 2.

    Thanks and regards,
    --P J S

  8. #8
    Join Date
    Mar 2007
    Posts
    626
    Hi,

    Thank you for providing sample data. However, I did not find CREATE TABLE statements, so I suppose that all columns have NUMBER data type.

    Instead of fetching from TIM_EMP again and again, what about storing its content in a collection variable and make a simple logic of assigning an element at required position, something like:
    Code:
    declare
      CURSOR cGetComplaints IS
        SELECT cc.*, row_number() over ( order by cc.customer_id ) rn
        FROM customer_complaints cc
        WHERE cc.engineer_id = 15  -- remove if you want to UPDATE all rows in table
                                   -- do you really want to do that???
        FOR UPDATE of engineer_id; -- what if anybody else updates these row(s) during this action?
    
      jr_eng_list sys.odcinumberlist;
      jr_eng_index integer;
    begin
      select te.jr_engineer_id bulk collect into jr_eng_list
      from tim_emp te
      where te.engineer_id = 15;
    
      FOR vGetComplaints IN cGetComplaints LOOP
        jr_eng_index := mod( trunc( (vGetComplaints.rn-1)/2 ), jr_eng_list.count )+1;
        update customer_complaints r
        set r.engineer_id = jr_eng_list( jr_eng_index )
        where r.customer_id = vGetComplaints.customer_id;
      END LOOP;
    end;
    /
    I am assuming that all value from TIM_EMP shall be assigned (each one twice in its turn), and that CUSTOMER_ID is unique - on which your code depended too (just deducing, I did not dare to analyze it deeper).

    Also I am not sure what shall be the result when CUSTOMER_COMPLAINTS.ENGINEER_ID is different than 15 - as you are filtering only TIM_EMP on that value. For safety, I added WHERE condition to the cursor not to allow update rows with different ENGINEER_IDs. I also added FOR UPDATE clause to prohibit any modification from other users during this transaction.

  9. #9
    Join Date
    Jul 2013
    Posts
    24
    Sir, It's Fabulous...Thanks alot.
    I had not thought of this option of "collection variable and make a simple logic of assigning an element at required position".
    Need to learn a lot from you....
    Thanks for your efforts...

    --P J S

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An update statement worked on DB2.
    Though I don't know it may work on Oracle,
    I'll show the example for your reference.

    Before update:
    Code:
    SELECT * FROM customer_complaints
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          15
           1002         5002          15
           1003         5003          15
           1004         5004          15
           1005         5005          15
           1006         5006          15
           1007         5007          15
           1008         5008          15
           1009         5009          15
           1010         5010          15
    
      10 record(s) selected.
    Update statement:
    Code:
    ------------------------------ Commands Entered ------------------------------
    UPDATE
    (SELECT customer_id , engineer_id
          , ROW_NUMBER() OVER(ORDER BY customer_id) AS row_num 
      FROM  customer_complaints
    ) AS cc
       SET  engineer_id
          = (SELECT jr_engineer_id
              FROM  LATERAL
                    (SELECT jr_engineer_id
                          , ROW_NUMBER() OVER(ORDER BY jr_engineer_id) AS row_num
                      FROM  tim_emp te
                      WHERE te.engineer_id = cc.engineer_id
                    ) te
              WHERE te.row_num = MOD( (cc.row_num - 1) / 2 , 2 ) + 1
            )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    After update:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM customer_complaints;
    ------------------------------------------------------------------------------
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          20
           1002         5002          20
           1003         5003          21
           1004         5004          21
           1005         5005          20
           1006         5006          20
           1007         5007          21
           1008         5008          21
           1009         5009          20
           1010         5010          20
    
      10 record(s) selected.

  11. #11
    Join Date
    Jul 2013
    Posts
    24
    Hi,

    Thanks for your reply
    I'll try this on oracle.

    --P J S

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought there were (at least two) points to be amended for Oracle.

    (1) Remove LATERAL.
    (2) Add TRUNC to "(cc.row_num - 1) / 2"
    (same as flyboy's example)

    Code:
    UPDATE
    (SELECT customer_id , engineer_id
          , ROW_NUMBER() OVER(ORDER BY customer_id) AS row_num 
      FROM  customer_complaints
    ) AS cc
       SET  engineer_id
          = (SELECT jr_engineer_id
              FROM  LATERAL
                    (SELECT jr_engineer_id
                          , ROW_NUMBER() OVER(ORDER BY jr_engineer_id) AS row_num
                      FROM  tim_emp te
                      WHERE te.engineer_id = cc.engineer_id
                    ) te
              WHERE te.row_num = MOD( (cc.row_num - 1) / 2 , 2 ) + 1
            )
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How ever now I am trying do it for unknown number of Jr_engineer records, instead of 2.

    What result do you want, if number of Jr_engineer records was 3.

    For example:
    SELECT jr_engineer_id FROM TIM_emp where engineer_id=15;
    20
    21
    22

    Required output:
    (A)
    customer_id complaint_id engineer_id
    1001 5001 20
    1002 5002 20
    1003 5003 21
    1004 5004 21
    1005 5005 22
    1006 5006 22
    1007 5007 20
    1008 5008 20
    1009 5009 21
    1010 5010 21

    or

    (B)
    customer_id complaint_id engineer_id
    1001 5001 20
    1002 5002 20
    1003 5003 20
    1004 5004 21
    1005 5005 21
    1006 5006 21
    1007 5007 22
    1008 5008 22
    1009 5009 22
    1010 5010 20

  14. #14
    Join Date
    Jul 2013
    Posts
    24
    Hi,

    I want to produce output same as (A).
    But is it possible to produce output described in (B) using single query?

    Thanks and regards,
    --P J S...

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to produce output same as (A).
    Tested on DB2 Express-C 9.7.5 for Windows.

    Before update:
    Code:
    SELECT * FROM customer_complaints
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          15
           1002         5002          15
           1003         5003          15
           1004         5004          15
           1005         5005          15
           1006         5006          15
           1007         5007          15
           1008         5008          15
           1009         5009          15
           1010         5010          15
    
      10 record(s) selected.
    Update statement:
    Code:
    UPDATE
    (SELECT customer_id , engineer_id
          , ROW_NUMBER() OVER(ORDER BY customer_id) AS row_num 
      FROM  customer_complaints
      WHERE engineer_id = 15 
    ) AS cc
     SET   engineer_id
         = (SELECT jr_engineer_id
             FROM  LATERAL
                   (SELECT jr_engineer_id
                         , ROW_NUMBER() OVER(ORDER BY jr_engineer_id) AS row_num
                         , COUNT(*)     OVER()                        AS row_cnt
                     FROM  tim_emp te
                     WHERE te.engineer_id = cc.engineer_id
                   ) te
             WHERE te.row_num
                   = MOD( TRUNC((cc.row_num - 1) / 2) , te.row_cnt ) + 1
           )
    ;
    After update:
    Code:
    SELECT * FROM customer_complaints
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          20
           1002         5002          20
           1003         5003          21
           1004         5004          21
           1005         5005          22
           1006         5006          22
           1007         5007          20
           1008         5008          20
           1009         5009          21
           1010         5010          21
    
      10 record(s) selected.

    But is it possible to produce output described in (B) using single query?
    Tested on DB2 Express-C 9.7.5 for Windows.

    Before update:
    Code:
    UPDATE customer_complaints
     SET   engineer_id = 15
    ;
    SELECT * FROM customer_complaints;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    SELECT * FROM customer_complaints
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          15
           1002         5002          15
           1003         5003          15
           1004         5004          15
           1005         5005          15
           1006         5006          15
           1007         5007          15
           1008         5008          15
           1009         5009          15
           1010         5010          15
    
      10 record(s) selected.
    Update statement:
    Code:
    UPDATE
    (SELECT customer_id , engineer_id
          , ROW_NUMBER() OVER(ORDER BY customer_id) AS row_num 
      FROM  customer_complaints
      WHERE engineer_id = 15 
    ) AS cc
     SET   engineer_id
         = (SELECT jr_engineer_id
             FROM  LATERAL
                   (SELECT jr_engineer_id
                         , ROW_NUMBER() OVER(ORDER BY jr_engineer_id) AS row_num
                         , COUNT(*)     OVER()                        AS row_cnt
                     FROM  tim_emp te
                     WHERE te.engineer_id = cc.engineer_id
                   ) te
             WHERE te.row_num
                   = MOD( TRUNC((cc.row_num - 1) / te.row_cnt) , te.row_cnt ) + 1
           )
    ;
    After update:
    Code:
    SELECT * FROM customer_complaints
    
    CUSTOMER_ID COMPLAINT_ID ENGINEER_ID
    ----------- ------------ -----------
           1001         5001          20
           1002         5002          20
           1003         5003          20
           1004         5004          21
           1005         5005          21
           1006         5006          21
           1007         5007          22
           1008         5008          22
           1009         5009          22
           1010         5010          20
    
      10 record(s) selected.

Posting Permissions

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