2616 error numeric overflow with row_number()

Database
Enthusiast

2616 error numeric overflow with row_number()

I'm trying to select a table having 238,21,78,900 records and I m generating sequence number with that as mentioned below.

Select cast(row_number over(order by col_name) as bigint
From table_name

And it is throwing me error of numeric overflow.
I tried to cast it with decimal(18,0) also, but still the same error.

Please help ASAP.
4 REPLIES

Re: 2616 error numeric overflow with row_number()

try decimal(38,0)

Senior Apprentice

Re: 2616 error numeric overflow with row_number()

Seems like the resulting datatype of ROW_NUMBER can't be changed (of course INTEGER is quite stupid), but ROW_NUMBER is just a shortcut for a COUNT:

CAST(COUNT(*) AS BIGINT) OVER (ORDER BY col_name)

I don't know if previous code also overflows, but the next will definitely work:

SUM(CAST(1 AS BIGINT)) OVER (ORDER BY col_name ROWS UNBOUNDED PRECEDING)
Enthusiast

Re: 2616 error numeric overflow with row_number()

Thank you so much dnoeth..
Your second query worked.
Can you please tell what it is doing if you don't mind.
Senior Apprentice

Re: 2616 error numeric overflow with row_number()

It's a cumulative sum of 1s :-)

But I just noticed that the COUNT was wrong, should also include ROWS:

CAST(COUNT(*) AS BIGINT) OVER (ORDER BY col_name ROWS UNBOUNDED PRECEDING)