GET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong output

Database
Enthusiast

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

3 REPLIES
Junior Contributor

Re: GET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong output

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

Enthusiast

Re: GET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong output

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

Junior Contributor

Re: GET DIAGNOSTICS v_Variable_Name = ROW_COUNT giving wrong output

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.