Comparing row count of two tables called by cursor

General
Enthusiast

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

4 REPLIES
Teradata Employee

Re: Comparing row count of two tables called by cursor

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;

Enthusiast

Re: Comparing row count of two tables called by cursor

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;

Enthusiast

Re: Comparing row count of two tables called by cursor

Is there any provision in Teradata like

%ROWCOUNT  cursor attribute in Oracle PL/SQL?

 

Could you please provide the link to Teradata cursor attributes?

Teradata Employee

Re: Comparing row count of two tables called by cursor

Please start a new topic for a new question.

 

Teradata does not have cursor attributes, but the value of ACTIVITY_COUNT will be set when you OPEN a cursor.