Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
Fields used in ON condition of JOIN have an explicit alias
Robbert Jan Grootjans
2010-10-08 03:32:31.0

When executing a query with the following syntax:
SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"

The resultset contains a column with name 'a.barid' in stead of the expected 'barid', as the column is not ambiguous in this context.
Re:Fields used in ON condition of JOIN have an explicit alias
HXTT Support
2010-10-08 06:11:38.0
>SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
Test. It's normal.

Both of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName will return BARID when bar table has NOT the same column name.

Both of ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName will return a.BARID when bar table HAS the same column name.

SELECT a."barid" AS BARID from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id" will return BARID any condition:)
Re:Fields used in ON condition of JOIN have an explicit alias
HXTT Support
2010-10-08 06:12:11.0
>ResultSetMetaData.getColumnName and ResultSetMetaData.getColumnName
It should be ResultSetMetaData.getColumnLabeland ResultSetMetaData.getColumnName
Re:Fields used in ON condition of JOIN have an explicit alias
Robbert Jan Grootjans
2010-10-08 06:29:54.0

True, I'm sorry, the expectation that we had was one would get 'a.BARID' when two
1. 'bar' table and 'foo' table both have the column 'barid'
2. both have been selected in the query

when only one of the columns has been selected in the query, the result would be 'barid'

so:
SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'a.barid' and 'b.barid' (instead of an error that the query was ambiguous, which you get in most DBMS's)

SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'barid'

I'm sorry I was unclear the first time.



Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias
Robbert Jan Grootjans
2010-10-11 06:49:34.0

I am unsure if you guys interpretted my follow-up as an aknowledgement or a new question. Let me state the following. I understand the way you describe it should work, but I would was expecting a slightly different approach.

In you statement you say the mandatory aliasing triggered by the presence of two identical fields in joined tables. I would expect it to be triggered in the situation that two identical fields are selected. So:
SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'a.barid' and 'b.barid' (instead of an error that the query was ambiguous, which you get in most DBMS's)

SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
would result in 'barid'

My apologies for replying in such a hasty fashion and assuming you missed my point, but you guys always respond really fast ;)
Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias
HXTT Support
2010-10-11 07:19:40.0
>so:
>SELECT a."barid", b."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
>would result in 'a.barid' and 'b.barid' (instead of an error that the query was
>ambiguous, which you get in most DBMS's)
Yeah. Supported.

>SELECT a."barid" from "foo" a LEFT JOIN "bar" b ON a."barid" = b."id"
>would result in 'barid'
It will resulted barid only when b hasn't barid column, and will resulted a.barid when b has a barid column. Sorry. It seems that you wish to get barid also when b has a barid columen ?
Re:Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias
Robbert Jan Grootjans
2010-10-11 07:50:03.0

Exactly, I would expect to get barid when a AND b have a barid column, but only one is selected in the result.

If both are selected I would expect a.barid and b.barid.

I'm not saying my wishes or expectations are correct, but this differs from other SQL/JDBC implementations we support as they do have a similar behaviour (though they throw an exception in the case a statement is ambiguous).

thanks for the quick response!
Re:Re:Re:Re:Re:Re:Re:Fields used in ON condition of JOIN have an explicit alias
HXTT Support
2010-10-12 19:26:51.0
>I'm not saying my wishes or expectations are correct
Supported. Please download the latest package.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap