All Forums Database
hercules 5 posts Joined 12/14
12 Jan 2015
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.

amik_xyz 3 posts Joined 03/11
12 Jan 2015

try decimal(38,0)

dnoeth 4628 posts Joined 11/04
12 Jan 2015

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)

 

Dieter

hercules 5 posts Joined 12/14
13 Jan 2015

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

dnoeth 4628 posts Joined 11/04
13 Jan 2015

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)

 

Dieter

You must sign in to leave a comment.