question

Katie 1 avatar image
Katie 1 asked

Implementing the user input and rownumber in sql server from Oracle

Hello all,

I am actually working on the migration of some of the stored procedures from Oracle to sqlserver 2008, and came across one stored procedure, which is actually used to work with one of the datastage jobs. The requirement of the stored procedure:>
is There are 22 million rows in a table and stored procedure is intended to extract the data based on the user input
for example, if the user input given is 1, the stored procedure is needs to get the first 2 million records out of the 22 million records,if the user input given 2 then the next set 2 million records(i.e from 2 mill to 4 million) are required to be fetched based on the row number and so on untill it reaches 22 million> here is the sample query.

select * 
   from ( select /*+ FIRST_ROWS(n) */a.*, ROWNUM rnum 
   from ( select * from t   order by id) 
   where ROWNUM <= :MAX_ROW_TO_FETCH 
   where rnum  >= :MIN_ROW_TO)_FETCH;

Any help is appreciated!

sql-server-2008t-sqloracle
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Fatherjack avatar image
Fatherjack answered

can you not use a lookup table to give the user provided value with an upper and lower limit? 

Create table RowSelectorReference 
(
UserParam int not null,,
LowrLimit int not null
UpprLimit int not null
)
GO

Insert into RowSelectorReference 
select 1, 0, 2000000
union
select 2, 2000001, 4000000
union
select 3, 4000001, 6000000
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

use a cte with row_number function

;with cte (rownum,col1, col2,...coln) as 
 (
  select row_number()over (order by id) as rownum, col1, col2, ..., coln
  from t
 )

select col1, col2,...coln 
from cte 
where rownum between MIN_ROW_TO_FETCH and MAX_ROW_TO_FETCH
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
ah, ooh, oh. Rownumbers needed. That rules out my suggestion then. well spotted Kev
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
obviously not.......
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.