This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Limiting # of records returned From: Mike Baranski <mbaranski@celito.net> Date: Wed, 06 Dec 2000 15:34:56 -0500 I've got an import program written in perl, which connects to an Oracle database and generates a flat file for peoplesoft to do an import. Does anyone know of an sql statement that will allow me to limit hte number of records returned for oracle? select * from theDB.badge limit 100 or something like that? === Subject: RE: Limiting # of records returned From: "Sterin, Ilya" <Isterin@ciber.com> Date: Wed, 6 Dec 2000 12:37:37 -0700 Either use the where clause on a field that will limit to amount you want or use $sth->finish() in your fetch loop to stop the loop operation and exit the loop with next(); Ilya Sterin -----Original Message----- From: Mike Baranski To: dbi-users@isc.org Sent: 12/06/2000 1:34 PM Subject: Limiting # of records returned I've got an import program written in perl, which connects to an Oracle database and generates a flat file for peoplesoft to do an import. Does anyone know of an sql statement that will allow me to limit hte number of records returned for oracle? select * from theDB.badge limit 100 or something like that? === Subject: RE: Limiting # of records returned From: "Tim Harsch" <harsch1@llnl.gov> Date: Wed, 6 Dec 2000 11:43:52 -0800 select * from theDB.badge where rowid <= 100 === Subject: RE: Limiting # of records returned From: Kuo David <dkuo@orchid.com> Date: Wed, 6 Dec 2000 14:48:45 -0500 I think it should be rownum instead of rowid. Isn't it? select * from theDB.badge where rownum <= 100 David Kuo -----Original Message----- From: Tim Harsch [mailto:harsch1@llnl.gov] Sent: Wednesday, December 06, 2000 2:44 PM To: Mike Baranski; dbi-users@isc.org Subject: RE: Limiting # of records returned select * from theDB.badge where rowid <= 100 === Subject: RE: Limiting # of records returned From: "Tim Harsch" <harsch1@llnl.gov> Date: Wed, 6 Dec 2000 12:07:53 -0800 Also, I lack the experience to know whether the pseudo column "rowid" would help in multi-table joins, sorts, etc. $sth->finish is a good method. There could be a battle for performance between the two. For instance on a sort finish would have to wait for the server to do the complete sort, but would the server be able to do a partial sort for a query limited by row id? Probably too much detail for this question. $sth->finish is a good method. === Subject: FYI: Limiting # of records returned From: Mike Baranski <mbaranski@celito.net> Date: Wed, 06 Dec 2000 16:16:10 -0500 Thanks for all of the help, just fyi, this was a view, and the ... rowid <= nnn did not work, I don't exactly know why... But, I did put a counter into the fetchrow_array loop, and it works great! === Subject: RE: Limiting # of records returned(OT: Oracle-specific) From: "Sarnowski, Chris" <csarnows@CuraGen.com> Date: Wed, 6 Dec 2000 15:21:42 -0500 Sorry for the OT post, but many people have misconceptions about rownum, so this is intended to save some grief. In Oracle, you can use the pseudocolumn rownum to limit the number of rows returned. select field from table where rownum <= 100; But there are limitations of rownum: (!!!) 1) You can't use rownum with order by, and get the results you might expect. The order by is applied after the result set is obtained, and since Oracle is a relational database, the result set may be returned in any order. If you want a particular set of 100 rows, you have to do something like the following: select field from (select t.field, rownum rnum from table t order by 1) where rnum <= 100; 2) You can't select the nth record with select field from table where rownum = n; where n > 1. You have to use a query similar to the nested query above. If you want a fuller explanation, send me a message off-list. I would not be surprised if it's in the archives of this list (just checked: it does come up periodically), and is or ought to be on any worthwhile Oracle DB website, since it is so frequently asked. On the other hand, in perl, there's no reason not to go procedural and just stop fetching when you have as many rows as you want. -Chris === Subject: RE: Limiting # of records returned From: "Dewis, Gordon - GEO/GEO" <gordon.dewis@statcan.ca> Date: Wed, 6 Dec 2000 15:13:12 -0500 On page 303 of _Programming the Perl DBI_, there is a note that there are "some frustrating limitations" with Oracle's ROWNUM. Personally, I'd count the number of records and do a finish() when you have all you want. === Subject: RE: Limiting # of records returned From: Kuo David <dkuo@orchid.com> Date: Wed, 6 Dec 2000 16:00:07 -0500 It is working for view as well as table. The key point here is you should use the pseudocolumn rownum instead of rowid. You will get "ORA-00932: inconsistent datatypes" error if you use "select ... from.. where rowid <= 100". David Kuo === Subject: RE: Limiting # of records returned(OT: Oracle-specific) From: "Tim Harsch" <harsch1@llnl.gov> Date: Wed, 6 Dec 2000 13:21:08 -0800 OK. I get rownum to work in most cases but by this message below it looks sort of as if ROWID is a legitimate keyword? Is that true or is just ROWNUM ... SQL> select * from DBA_INDEXES where rowid < 10 2 ; select * from DBA_INDEXES where rowid < 10 * ERROR at line 1: ORA-01445: cannot select ROWID from a join view without a key-preserved table === Subject: Re: Limiting # of records returned(OT: Oracle-specific) From: Ronald J Kimball <rjk-dbi@focalex.com> Date: Wed, 6 Dec 2000 16:29:31 -0500 On Wed, Dec 06, 2000 at 01:21:08PM -0800, Tim Harsch wrote: > > OK. I get rownum to work in most cases but by this message below it looks > sort of as if ROWID is a legitimate keyword? Is that true or is just ROWNUM > ... > > > SQL> select * from DBA_INDEXES where rowid < 10 > 2 ; > select * from DBA_INDEXES where rowid < 10 > * > ERROR at line 1: > ORA-01445: cannot select ROWID from a join view without a key-preserved > table > This is from Oracle8: The Complete Reference: Pseudo-Columns ... RowID Returns the row identifier for a row. Use the RowID in in the UPDATE ... WHERE and SELECT ... FOR UPDATE. This guarantees that only a certain row is updated, and no others. RowNum Returns the sequence number in which a row was returned selected from a table. The first row RowNum is 1, the second is 2, and so on. An order by will affect the sequence of the ROWNUMs. ... ... Note that row in a table has a specific RowID, while RowNum depends on a select statement and the order the rows are returned in. Here's what RowIDs look like: select rowid from list ROWID ------------------ AAAAd4AAHAAACyVAAA AAAAd4AAHAAACyVAAB AAAAd4AAHAAACyVAAC AAAAd4AAHAAACyVAAD AAAAd4AAHAAACyVAAE AAAAd4AAHAAACyVAAF AAAAd4AAHAAACyVAAH AAAAd4AAHAAACyVAAI AAAAd4AAHAAACyVAAJ AAAAd4AAHAAACyVAAL AAAAd4AAHAAACyVAAM ===