how to use ROWNUM to retrieve a row?

[elaine3839]

Hi,

I was trying to use ROWNUM to retrieve a row but got no luck. What I did was:

1. Find the max ROWNUM by doing

select max(ROWNUM) from callhistory;

MAX(ROWNUM)
-----------
1414980

2. Use this ROWNUM to retrieve a row

select calldate from callhistory where rownum = 1414980;

no rows selected

Can you tell me why it's not working?

Thanks!

[pipo]

ronwum can be used with '=' sign only when using rownum=1, else you need to use rownum<=value, which will give you all the "value" first lines ...

but the good question to ask is : why do you want to use rownum, it only gives you the order in which the lines are retrieved by your query, what exactly do you want to do ???

[elaine3839]

Thanks pipo!

My migration of a big CallHistory table from SQL Server to Oracle was failed in the middle(out of memory) -- there are totally 12 million rows need to be migrated while only 10% of them was moved over.

I created a 2nd table in SQL Server which is the same as the original one except that it was grouped by CallDate (which I think that will make the CallDate column to be ordered). I was trying to use ROWNUM to find out what the last CallDate that has been migrated to Oracle, so I can just pick up from there.

Does that make sense to you?

[helpme]

go get a Nth record do this

select * from table1 where rownum=1 and rowid NOT IN
(SELECT rowid FROM table1 WHERE rownum < 12345);

[pipo]

in such a case, I'd try to find a number column (an id for example) in the table, and then migrate the rows block by block using a modulo function.
for example let's say you have an id column which is a number, I'd first migrate all rows which id ends with a 1 (that is to say mod(id,10)=1), then all lines which id ends with 2, 3, and so on ... that way you only manipulate about 10% of the lines each time and you won't saturate your memory ...
note that if it's still too much, you can use a number greater than 10 in your modulo function, that will reduce the number of lines in each "block" ...

[elaine3839]

I looked at the table's data, and none of the NUMBER columns' data has a nice distribution :-( So I will just 'chop' the big table into a couple of smaller managable pieces, and migrate them one at a time.

Thanks again for your help!