Re: Using ROWNUM

"Reds" <redex1398_at_hotmail.com> schreef in bericht news:redex1398-64C0CF.03564604102007_at_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

This is called a contradictio in terminis: You ask for *one* row, expecting rownum =*2*...... II think you should use an inline query like

select ... from
(select *, rownum the_number from table t1) where t1.the_number = 2

Furthermore: if you don't use anything like order by, what's the definition of row 1, row 2 etc?

And third: I think your loop of which you say it is working, will return the same row over and over again.... (if you take out the EXIT clause) To achieve want I think you want to, use a cursor for loop.....

Shakespeare Received on Thu Oct 04 2007 - 05:11:52 CDT