All Forums General
gannu 8 posts Joined 02/13
15 May 2013
Comparing row count of two tables called by cursor

Hi,
I am trying to compare the row count of table in two different databases but the tables are similar with same DDL. I need to call table name in cursor and database names are parameters of the procedure.  
Replace procedure testProc(a varchar(30),b varchar(30))
BEGIN
-- Cursor is having the names of table in order
OPEN MY_CURSOR ;
WHILE (SQLCODE = 0) DO
FETCH MY_CURSOR INTO T_NAME;
END
 
Now I have to comapre the row count of a.T_name and b.T_name. If they are equal continue and if they are not equal register an error and continue.
 
Can you please help me in this. Any help would be appreciated.
Thanks
 
 

Fred 1096 posts Joined 08/04
15 May 2013

You need to use "dynamic SQL that returns a result set", something like this:
 
DECLARE SqlStr VARCHAR(1000);
DECLARE C1 CURSOR FOR S1;
SET SqlStr = 'SELECT COUNT(*) FROM '||a||'.'||T_name;
PREPARE S1 FROM SqlStr;
OPEN C1;
FETCH C1 INTO DB_A_CT;
CLOSE C1;
 

gannu 8 posts Joined 02/13
16 May 2013

Thanks Fred,
 
But i was thinking to create a volatile table and insert two values of count of two tables and compare. If duplicate values register error. And at end of loop it will delete the values. It will do the same for all T_NAME. Is this a good idea.
 
SET V_T_Name = 'INSERT INTO volatiletable SELECT COUNT(*) FROM ' || A || '.' || T_NAME || ';' ;
 
CALL DBC.SysExecSQL(:V_T_Name);
 
SET V_T_Name = 'INSERT INTO volatiletable SELECT COUNT(*) FROM ' || B|| '.' || T_NAME || ';' ;
 
CALL DBC.SysExecSQL(:V_T_Name);
 
SEL COUNT(*) INTO k FROM volatiletable
GROUP BY rowcount
HAVING ( COUNT(rowcount) > 1 );
 
DEL FROM Volatiletable ALL;
 
 
 

You must sign in to leave a comment.