All Forums Database
parthmalhan 26 posts Joined 09/14
24 Dec 2014
GET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong output

Hi,
ROW_COUNT is returning wrong number.
i have a table having around 100K rows. In a procedure i'm updating all the rows and returning number of rows affected.
I'm using following query to get number of affected rows.
 
GET DIAGNOSTICS v_Variable_Name = ROW_COUNT;
 
when number of rows in table is upto 32767, procedure returns correct value.
When table get 32768 rows then it returns -32768 and then -32767...
is it returning Smallint datatype. If yes, how can i fix this.
 
Regards,
Parth Malhan

dnoeth 4628 posts Joined 11/04
30 Dec 2014

Hi Parth,
can you show the DECLARE v_Variable_Name?
Might be defined as a SMALLINT and there's no error returned when it overflows...

Dieter

parthmalhan 26 posts Joined 09/14
30 Dec 2014

Thanks for your response.
PFB procedure that i tested for this.
 
REPLACE procedure test()
dynamic result sets 1
begin
    DECLARE dg_rowcnt integer;
    set dg_rowcnt = 0;
    update RowCountTest set COLUMN1=COLUMN2;
    GET DIAGNOSTICS dg_rowcnt = row_count;
    begin
        DECLARE cur CURSOR WITH RETURN ONLY TO CLIENT FOR
            select dg_rowcnt;
        OPEN CUR;
    end;
end;
 
table RowCountTest has 100000 rows and this procedure returned -31072.
 
Regards,
Parth Malhan

dnoeth 4628 posts Joined 11/04
31 Dec 2014

Hi Parth,
I just tried it on a 15.00.01.04 and it was the same.
ROW_COUNT is defined as an INT (why not as BIGINT?), but seems to return the range of a SMALLINT. 
I didn't notice that before, because I use ACTIVITY_COUNT instead, which returns the correct value.
You should open an incident with Teradata support.
 

Dieter

You must sign in to leave a comment.