Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Red face Unanswered: How to compare the values from a table with a cursor in DB2

    Hi

    How to compare the values in the tables which has repeative rows with a cursor variable.

    BEGIN
    DECLARE acc_imp_cur CURSOR WITH RETURN FOR
    SELECT ACCOUNT_ID,CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED ;

    DECLARE comp_cur CURSOR WITH RETURN FOR SELECT
    DISTINCT CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED;

    OPEN acc_imp_cur;
    OPEN comp_cur;
    --LOOP

    FETCH acc_imp_cur INTO v_str,v_control_id;
    FETCH comp_cur INTO v_control_id1;


    SET v_str = CONCAT(v_str,',');

    WHILE(v_control_id = v_control_id1) DO

    IF(LOCATE (',',v_str) > 0) THEN

    this is the scenario how will i compare the values and do the manipulation.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What exactly do you try to do? What's your data and what do you want to compare with what? And what are those "manipulations" you mention?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    ID SON ACC
    141 1 4511039999
    141 1 131XXXXXXX
    141 1 4411109999
    141 1 5940029999
    141 1 5940049999
    141 1 4431009999
    166 1 4511039999
    166 1 131XXXXXXX
    166 1 4411109999
    166 1 5940029999
    166 1 5940049999
    166 1 4431009999
    131 1 4511039999
    131 1 131XXXXXXX
    131 1 4411109999
    131 1 5940029999
    131 1 5940049999
    131 1 4431009999
    124 1 4511039999
    124 1 131XXXXXXX
    124 1 4411109999
    124 1 5940029999
    124 1 5940049999

    my table data is like that one above , now i am trying to write a strored procedure to combine all the ACC's in the same ID into another table like

    124 4511039999,131XXXXXXX,4411109999,5940029999

    So in my stored procedure i fetched the ID and ACC's in a cursor and then how will i compare with the id's in the table through a loop


    CREATE PROCEDURE GET_ACCOUNTS_IMPACTED ()
    DYNAMIC RESULT SETS 1
    BEGIN

    DECLARE v_str varchar(100);
    DECLARE v_acc_imp varchar(1000);
    DECLARE v_control_id integer;
    DECLARE v_control_id1 integer;

    BEGIN
    DECLARE acc_imp_cur CURSOR WITH RETURN FOR
    SELECT ACCOUNT_ID,CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED ;

    DECLARE comp_cur CURSOR WITH RETURN FOR SELECT
    DISTINCT CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED;

    OPEN acc_imp_cur;
    OPEN comp_cur;

    FETCH acc_imp_cur INTO v_str,v_control_id;
    FETCH comp_cur INTO v_control_id1;

    SET v_str = CONCAT(v_str,',');

    WHILE(v_control_id = v_control_id1) DO

    IF(LOCATE (',',v_str) > 0) THEN
    SET v_acc_imp = CONCAT(v_str,v_str);
    ELSE
    SET v_acc_imp = UPPER(v_str);
    END IF;
    SET v_acc_imp = v_acc_imp;
    END WHILE;

    INSERT INTO DUMMY_CAI1 (CONTROL_ID,ACCOUNTS_IMPACTED)
    VALUES(v_control_id1,CASE WHEN v_acc_imp IS NULL THEN ' ' ELSE v_acc_imp END);
    CLOSE acc_imp_cur;
    END;
    END

    this procedure is going into an infinite loop please give me a suggestion to loop through the values and get the code if any one knows
    ASAP................

    thanks in advance....

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    How about such a logic ?


    Declare Cursor c1 FOR
    SELECT ACCOUNT_ID,CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED
    ORDER BY ACCOUNT_ID

    OPEN C1

    FETCH C1

    DO WHILE ( SQLCODE=0 )
    SAVE_ID = ACCOUNT_ID
    V_Acct = ''

    DO WHILE ( SQLCODE=0 & SAVE_ID = ACCOUNT_ID )
    IF V_Acct = '' THEN V_Acct = CONTROL_ID
    ELSE V_Acct = V_Acct CONCAT ',' CONCAT CONTROL_ID

    FETCH C1
    END_DO_WHILE

    INSERT INTO DUMMY1_CAI1 VALUES ( SAVE_ID , V_Acct )
    END_DO_WHILE

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you considered using a common table expresssion...
    See an example below :
    Code:
    with order_details(order_number,item)  
    as 
    ( 
    values(101,'Shoes'),(101,'Umbrella'),(101,'Coat'), 
    (102,'Hat'),(103,'Umbrella'),(103,'Jumper') 
    ), 
    order_det_seq(order_number,item,order_item_seq)  
    as  
    ( 
    select order_number,item,rownumber() over (partition by order_number) from order_details 
    ), 
    serialized_order_details(s_order_number,s_serialized_item,s_order_item_seq) as  
    ( 
    select order_number,varchar(item,1000),order_item_seq from order_det_seq where order_item_seq=1
    
    union all 
    select order_number,varchar(s_serialized_item,1000)||','||item,order_item_seq from
    order_det_seq c,serialized_order_details t where t.s_order_number=c.order_number
    and t.s_order_item_seq+1=c.order_item_seq 
    ) 
    select s_order_number,s_serialized_item from 
    ( 
    select s_order_number,s_serialized_item,rownumber() over (partition by s_order_number
     order by s_order_item_seq desc) as rn  from serialized_order_details 
    
    ) as t where rn=1
    In the above common table expression, you will not need the order_details table
    as you will have it as a permanent table.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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