This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization 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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic