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.

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?

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....

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

Have you considered using a common table expresssion...
See an example below :
In the above common table expression, you will not need the order_details table
as you will have it as a permanent table.