aspose file tools*
The moose likes JDBC and the fly likes Retrieving a subset of rows with SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieving a subset of rows with SQL" Watch "Retrieving a subset of rows with SQL" New topic
Author

Retrieving a subset of rows with SQL

Paul Bull
Ranch Hand

Joined: Feb 14, 2001
Posts: 37
Hi,
I have an SQL question. I hope this is the appropriate forum for it. I would like to know if there is any way to retrieve a set of rows from a table without using an identifying column ie. primary key. I want to be able to write a JDBC application where I can plug a table name into a query statement and retrieve
a set of rows,ie rows 50 to 100, without knowing any of the column name or values for that table. I tried writing:
SELECT *
FROM tablex
WHERE rownum BETWEEN 50 AND 100;
This doesn't work. I'd appreciate anyones advice.
Thanks,
Paul
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Paul,
The select you wrote should work unless your not using Oracle. Obviously there should be more than 50 rows for your statement also. Otherwise it is a valid statement.
It is generally considered bad practice to retrieve rows using the *, since the table can change. If you don't want to know the column names before hand, do some metadata calls to retrieve that information to create the select you need.
HTH
Dan
Paul Bull
Ranch Hand

Joined: Feb 14, 2001
Posts: 37
Dan,
Thanks very much for your reply. Do you happen to know how I could accomplish the task using Oracle? That seems to be our problem. We're trying to retrieve from an Oracle database.
Thanks again,
Paul
Paul Bull
Ranch Hand

Joined: Feb 14, 2001
Posts: 37
Dan,
I think I misread your reply. Did you say it should work if I'm using Oracle? In that case, we did try using the above statement. I tried it in SQLPlus first. It did not seem to work. Do you have any ideas what might be going on?
Thanks,
Paul
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Check the following...
Does tablex exists...check the systables
are there more than 49 rows?
!! do you have access to the table ? who is the owner of the table and what userid are you using to try and read it. If they are different, the grant must have been issued to access the table.
What error are you getting?
Dan
Paul Bull
Ranch Hand

Joined: Feb 14, 2001
Posts: 37
Hi Dan,
I have access to the table. It's just a small one we've been using to test with, its got 200 rows. The error I keep getting is "no rows selected". I am certain I'm not going over the total number of rows. I have no problem retrieving data on that table with other SQL statements.
Thanks,
Paul
[This message has been edited by Paul Bull (edited August 09, 2001).]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Paul:
From what I have tried in SQL*Plus, I can view rownum for every column but for some reason you can not compare the number in your where clause:
"select rownum, id, name from emp" returns
1, 00001, 'John'
2, 00002, 'Jim'
3, 00003, 'Bob'
but "select rownum, id, name from emp where rownum > 1" returns no rows???
Jamie
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Jamie is right.
If you bad between 1 and 50 it works. But it fails if you don't start at 1. We use sequences on our tables, so we use them all the time which would work.
Dan
Paul Bull
Ranch Hand

Joined: Feb 14, 2001
Posts: 37
Dan,Jamie
Thanks for your help.
Paul
Butch Car
Greenhorn

Joined: Jan 12, 2001
Posts: 13
What about something like:
Select id, name from
(select rownum rownumndx, id, name from emp)
where rownumndx>50 and rownumndx<100;
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieving a subset of rows with SQL