By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

How to compare the values from a table with a cursor in DB2

P: 1
Expand|Select|Wrap|Line Numbers
  1. ID         SON              ACC
  2. 141    1    4511039999
  3. 141    1     131XXXXXXX
  4. 141    1     4411109999
  5. 141    1     5940029999
  6. 141    1     5940049999
  7. 141    1     4431009999
  8. 166    1    4511039999
  9. 166    1     131XXXXXXX
  10. 166    1     4411109999
  11. 166    1     5940029999
  12. 166    1     5940049999
  13. 166    1     4431009999
  14. 131    1    4511039999
  15. 131    1     131XXXXXXX
  16. 131    1     4411109999
  17. 131    1     5940029999
  18. 131    1     5940049999
  19. 131    1     4431009999
  20. 124    1    4511039999
  21. 124    1     131XXXXXXX
  22. 124    1     4411109999
  23. 124    1     5940029999
  24. 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


Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE GET_ACCOUNTS_IMPACTED ()
  2.     DYNAMIC RESULT SETS 1
  3. BEGIN
  4.  
  5.     DECLARE v_str varchar(100);
  6.     DECLARE v_acc_imp varchar(1000);
  7.     DECLARE v_control_id integer;
  8.     DECLARE v_control_id1 integer;
  9.  
  10.     BEGIN
  11.     DECLARE acc_imp_cur CURSOR WITH RETURN FOR
  12.         SELECT ACCOUNT_ID,CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED ;
  13.  
  14.     DECLARE comp_cur CURSOR WITH RETURN FOR SELECT 
  15.          DISTINCT CONTROL_ID FROM CONTROL_ACCOUNTS_IMPACTED;
  16.  
  17.         OPEN acc_imp_cur;
  18.         OPEN comp_cur;    
  19.  
  20.         FETCH acc_imp_cur INTO v_str,v_control_id;
  21.         FETCH comp_cur INTO v_control_id1;                          
  22.  
  23.               SET v_str = CONCAT(v_str,',');
  24.  
  25.              WHILE(v_control_id = v_control_id1) DO
  26.  
  27.                 IF(LOCATE (',',v_str) > 0) THEN
  28.                     SET v_acc_imp = CONCAT(v_str,v_str);                    
  29.                     ELSE
  30.                         SET v_acc_imp = UPPER(v_str);
  31.                     END IF;                    
  32.                  SET v_acc_imp = v_acc_imp;         
  33.             END WHILE;
  34.  
  35.         INSERT INTO DUMMY_CAI1 (CONTROL_ID,ACCOUNTS_IMPACTED)
  36.         VALUES(v_control_id1,CASE WHEN v_acc_imp IS NULL THEN ' ' ELSE v_acc_imp END);                     
  37.     CLOSE acc_imp_cur;
  38.     END;
  39. 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....
Nov 28 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.