Accessing columns using fully qualified name from a Join

From: praskam (praskam_at_discussions.microsoft.com)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 07:07:05 -0700

1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
JDBC, Service Pack 2, Version 2.2.0037
2) The query I'm executing is:
select * from employees, dept, city where dept.dpid=employees.dept and " +
 "employees.cityofresidence=city.cid order by dept.dpid, empid
3) city and dept tables have a field called 'name'
4) rs.getString("name") gives dept.name (city.name if city appears before
dept in the FROM clause of the query)
5) rs.getString("city.name") throws exception - 'Invalid column name'
How can I use the fully qualified column name to retrieve correct values
irrespective of the order of the appearance of the table names in the FROM
clause of the query

Note: rs.getString(int col_num) always retrieves correct values.

Relevant Pages

  • Re: Need to tune a table for performance gains
    ... as long as you know what city a person is in. ... query to find out how many records that is. ... in when you are filtering on several columns. ... We create two _Mini ...
    (comp.databases.ms-sqlserver)
  • Re: Query Conflict
    ... the following query with good results. ... I now want to add an additional criteria of the city field. ... The conflict appears to be between the city criteria and the SUM ... the SELECT clause. ...
    (comp.databases.ms-access)
  • Re: Sorting a concatenated query problem
    ... Make sure the query has the City field in it as a standalone field. ... Bob, by View menu item, do you mean the green report icon with view ... StackedLastMidFirstAddressCITYALPHA, it does not provide the same ...
    (microsoft.public.access.gettingstarted)
  • Re: Why?
    ... I have to look at my query that couldn't pass the 8.0/9.0 test. ... Select Sum, City, State from Invoices Group By State ... (Yeah, lame example as queries go, but look at your non-compliant ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Why?
    ... I have occasionally used the technique of normalizing the underlying tables ... non-normal data. ... I have to look at my query that couldn't pass the 8.0/9.0 test. ... Select Sum, City, State from Invoices Group By State ...
    (microsoft.public.fox.programmer.exchange)
Loading