Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DISTINCT sql command and Invalid cursor error

 
david allen
Ranch Hand
Posts: 185
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
HI everyone,
Just need a bit of help with this error I keep getting.
I have written the following query.
rs = db.returnQuery("SELECT DISTINCT Venue.name FROM Venue, EventsVenues WHERE EventsVenues.venueID = Venue.venueID AND eventID = " + id);
while(rs.next())

venue.add(new Venue(rs.getString("name")))
***************
If I take out the DISTINCT word I dont get the error. The error is generated by rs.getString("name");
If someone could tell me why I would really appreciate it.
david
 
Dorothy Finkel-Laverty
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Usually, in Oracle, when you use the grouping functions, the name of the column changes - for example, if you said "SELECT a, count(b) from TABLE GROUP BY a", then the name of the column becomes COUNT(color), not color. I thought this might be the same issue, but when I tried a SELECT DISTINCT in Oracle, the name of the column appeared to be unchanged from the original. I did not have the same problem you saw, so I can't explain why it is happening, but I can suggest a solution.
The ResultSet.get methods all allow you to put EITHER the column name, OR a number. If you change the parameter to 1, I think you'll get what you want.
[ June 06, 2003: Message edited by: Dorothy Finkel-Laverty ]
 
Andy Bowes
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Another solution if you would prefer not to rely upon the order of the coulmns in the query is to explicity specify a name for the columns in the SQL query.
For example:
select count(*) as "Record Count" from MyTable
HTH
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic