Using ROWNUM

From: Reds <redex1398_at_hotmail.com>
Date: Thu, 04 Oct 2007 03:56:47 -0500
Message-ID: <redex1398-64C0CF.03564604102007@newsclstr03.news.prodigy.net>

Hi,
I'm having a lot of problems using ROWNUM. Specifically, using ROWNUM in the where clause to access a particular row. It seem that if I use it as "ROWNUM = 1 " or "ROWNUM > 0", it works. But when I use it as "ROWNUM = 2" or "ROWNUM > 1", no rows are selected.

Here are some query examples:

THIS WORKS: SQL> SELECT *
2 FROM Student
3 WHERE ROWNUM = 1; STU_ID LNAME FNAME M S MAJOR HOME ---------- ---------- ---------- - - ---------- ----

10001 Smith Ron M M Math Tx

SQL> SELECT *
2 FROM Student
3 WHERE ROWNUM > 0; STU_ID LNAME FNAME M S MAJOR HOME ---------- ---------- ---------- - - ---------- ----

     10001 Smith      Ron        M M Math       Tx
     10002 Jones      Peter      A M English    Tx
     10003 Peters     Anne       A F English    Me
     10004 Johnson    John       J M CompSci    Ca
     10005 Penders    Alton      P F Math       Ga
     10006 Allen      Diane      J F Geography  Minn
     10007 Gill       Jennifer   F F CompSci    Tx
     10008 Johns      Roberta    A F CompSci    Ala
     10009 Wier       Paul       A M Math       Ala
     10010 Evans      Richard    A M English    Tx

10 rows selected.

BUT THIS DOESN'T WORK: SQL> SELECT *
2 FROM Student
3 WHERE rownum > 1;

no rows selected

SQL> SELECT *
2 FROM Student
3 WHERE ROWNUM = 2; no rows selected

Here are some PL/SQL exampleS

THIS WORK: SQL> set serveroutput on
SQL> DECLARE
  2
  3 v_ID Student.Stu_ID%TYPE;
  4 v_Rowcount NUMBER := 1;
  5
  6 BEGIN
  7
  8 LOOP
  9 SELECT Stu_ID
10 INTO v_ID
11 FROM Student
12 WHERE ROWNUM = 1;
13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID: '|| v_ID)
;

14 v_Rowcount := v_Rowcount + 1;
15
16 EXIT ;
17 END LOOP;
18 END;
19 /
Row Number : 1, Student ID: 10001

PL/SQL procedure successfully completed.

BUT THIS DOESN'T WORK: SQL> DECLARE
  2
  3 v_ID Student.Stu_ID%TYPE;
  4 v_Rowcount NUMBER := 1;
  5
  6 BEGIN
  7
  8 LOOP
  9 SELECT Stu_ID
10 INTO v_ID
11 FROM Student
12 WHERE ROWNUM = 2;
13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID: '|| v_ID)
;

14 v_Rowcount := v_Rowcount + 1;
15
16 EXIT ;
17 END LOOP;
18 END;
19 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 9

BTW, does anyone has a suggestion as to how to use the SELECT INTO to sequentially access each row? I thought the ROWNUM would be a good parameter to use, but it's not cooperating.

Thanks Received on Thu Oct 04 2007 - 03:56:47 CDT