469,299 Members | 2,080 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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

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
0 1098

Post your reply

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

Similar topics

reply views Thread by Golawala, Moiz M (GE Infrastructure) | last post: by
reply views Thread by Peter Wang | last post: by
3 posts views Thread by bolly | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.