Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news.tele.dk!news.tele.dk!small.news.tele.dk!newsgate.cistron.nl!xs4all!transit2.news.xs4all.nl!post.news.xs4all.nl!not-for-mail From: "Shakespeare" Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server References: Subject: Re: Using ROWNUM Date: Thu, 4 Oct 2007 12:11:52 +0200 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-RFC2646: Format=Flowed; Original Lines: 143 Message-ID: <4704bc73$0$240$e4fe514c@news.xs4all.nl> NNTP-Posting-Host: 82.92.93.98 X-Trace: 1191492723 news.xs4all.nl 240 [::ffff:82.92.93.98]:49649 X-Complaints-To: abuse@xs4all.nl Xref: usenetserver.com comp.databases.oracle.misc:250109 comp.databases.oracle.server:435908 X-Received-Date: Thu, 04 Oct 2007 06:12:03 EDT (text.usenetserver.com) "Reds" schreef in bericht news: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 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