Expand|Select|Wrap|Line Numbers
- 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
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
- 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
ASAP................
thanks in advance....