I have a table which has thousand of records i want to
fetch only record num 100 to record num 200. Write a query
that satisfies this criteria.(Cant use any keys)
Anyone please reply ASAP!
- 17 Answers
- 24422 Views
- KNIT, Tech Mahindra, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / m4io
scrollable cursors
------------------
DECLARE cursor_name sensitivity SCROLL CURSOR FOR
SELECT ... FROM ...
open cursor
FETCH ABSOLUTE 100 FROM cursor_name
do 100 times
FETCH NEXT FROM cursor_name
| Is This Answer Correct ? | 7 Yes | 1 No |
Answer / mvramesh
How about combingin two queries with set operator EXCEPT,
if you have query A EXCEPT query B, the result would be A-B.
Select * from table A fetch first 200 rows only
Union except
Select * from table A fetch first 100 rows only
| Is This Answer Correct ? | 5 Yes | 2 No |
Answer / sudheer d
YA THE ANSWER IS BY USING CURSORS ONLY
declare
cursor USER_Cursor
is select *
from <table_name>;
y <TABLE_NAME>%ROWTYPE; --ITS A DATA TYPE INCLUDES
WHOLE ROW 4M A TABLE IN TO X
COUNT1 number(2);
begin
COUNT1:=1;
open USER_Cursor;
while USER_Cursor%FOUND AND COUNT1 <>101
loop
fetch USER_Cursor into y;-- JUST FETCH DONT DISPLY -
--TILL 101ST RECORD
COUNT1:=COUNT1+1;
end loop;
--NOW DISPLAY FROM 101 RECORD TO 200 RECORD
while USER_Cursor%FOUND AND COUNT1 <>201
loop
fetch USER_Cursor into y;
dbms_output.put_line(y);
COUNT1:=COUNT1+1;
end loop;
close USER_Cursor;
end;
| Is This Answer Correct ? | 3 Yes | 0 No |
Answer / vikatakavi08
select * from emp where rownum<=100 and rownum>=200;
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / sangeeta david
We can make use of Relative record number that is
Select * from Table A where RRN(A) between 100 and 200
| Is This Answer Correct ? | 3 Yes | 2 No |
Answer / the boss
There is no correct answer to this flawed question.
In RDBMS theory a table doesn't have 'records'; it is an
UNORDERED set of tuples ("rows"), so there is no
recordnumber 100 or 200.
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / venkat
select * from table where empno > 100 or ( select * from
table where empno < 200)
Some many ways to write a query for this, this is one way...
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / rajeshkumar
SELECT * FROM TCB732D.AR_AR WHERE NUM_SEQ_AR IN(
SELECT NUM_SEQ_AR FROM TCB732D.AR_AR ORDER BY NUM_SEQ_AR
FETCH FIRST 200 ROWS ONLY)
ORDER BY NUM_SEQ_AR FETCH FIRST 100 ROWS ONLY
| Is This Answer Correct ? | 0 Yes | 0 No |
What is a plan and package in db2?
Comment whether the cursor is closed during commit or not.
if one db2 pgm calls another db2 pgm. how many plans do we need. what r the steps involved..
What will happen, while trying to close the closed cursor.
Is db2 a mainframe?
Can we use select query in a loop to fetch multiple rows in a COBOL PROGRAM? If so, what is the advantage of cursor?
Can DB2 be accessed by TSO users? If yes, which command is used to invoke DB2?
how can i pull up a query which was previously stored in qmf
wht is d/f between union and joins ?
What is cursor with hold option in db2?
Hi, i would like to start a new carrier in DB2 DBA.Which and what all step should i have to take..?
What are catalog tables in db2?
COBOL 
