To calculate nth row using cursors....

[chinnaraj]

hai all,

i want logic how to calculate the nth row in a table using cursors...
plz helop me

[davide++]

Hi all.

As in your previous post, which I don't know if you have resolved, what you're trying to do is obscure.
First, what db are you using (Oracle, SqlServer, PincoPallino)?
Second, what means the nth row in a table using cursor? According by some specific order?
I suggest you to add more informations when you post: will be easier to help you.

[chinnaraj]

I already told u in the previous post am using ORACLE....

n cuming to question am passing an parameter to the cursor ...vch takes the no. of records to be retrieved...
but i want to show the last record i.e., nth record....

for example if i gave 6...i hav to display 6threcord.......

[davide++]

Well
First, previous post means "the last one where both joined in", ie

http://www.codeguru.com/forum/showthread.php?t=446379

and you didn't say what db were using. You said this in another thread, but one can't read all your posts to get the necessary information.
Second, please write in proper English: what's u (pheraps you) or vch (maybe which)? English isn't my tongue, so for me is more difficult reading your strange language.

However, probably you want a cursor with an integer parameter that say the nth record to computate.
Of course, this's PL/SQL code.
Take a look at this



-- Declare cursor
CURSOR crsMyCursor(pNthRecoord IN INTEGER) IS
SELECT ROWNUM,
FIELD_1,
FIELD_2
FROM MY_TABLE
WHERE FIELD_1 = 'SOMETHING'
ORDER BY FIELD_1;


-- Open cursor and read records
FOR recMyCursor IN crsMyCursor(6) LOOP
IF recMyCursor.ROWNUM = 6 THEN
Display(recMyCursor);
END IF;
END LOOP;



ROWNUM says the nth record extractded by queries; the ORDER BY clause is basic.
I never tried using ROWNUM within a cursor, so I don't know if this will work fine.
I hope this will help you.

[chinnaraj]

oHH!
sorry man...i didnt saw your display name....
i said in another post

http://www.codeguru.com/forum/showthread.php?t=445212


Thanks for suggesting me....and then i never use short cuts in english...
thanks and i got the result

One morething...sorry i forgot to mention .. i have to pass the value....
i mean the code which you gave above ...you pass the value as '6'...
but i need to pass the value at runtime....how....?
plz only one and last time......

[davide++]

OK OK don't worry.

My example is wrong, it don't use the parameter; anyway if you want to pass a value at runtime you'll have a variable, and you can pass the variable as well as a scalar.
Probably you don't need the parameter: you can write somthing like this:


-- Declare cursor
CURSOR crsMyCursor IS
SELECT ROWNUM,
FIELD_1,
FIELD_2
FROM MY_TABLE
WHERE FIELD_1 = 'SOMETHING'
ORDER BY FIELD_1;

-- Declare an integer variable
vMyVar INTEGER;

vMyVar := 6;

-- Open cursor and read records
FOR recMyCursor IN crsMyCursor LOOP
IF recMyCursor.ROWNUM = vMyVar THEN
Display(recMyCursor);
END IF;
END LOOP;