I get an unexpected query result when running a query that has two columns with the same name (from two different tables). I use column aliases for both columns.
In the support list of the HXTT DBF component I found the issue "Fields used in ON condition of JOIN have an explicit alias" (issued by Robbert Jan Grootjans, 2010-10-08 03:32:31.0) that it's meant to work like this:
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'
Well, I can understand that it works like this when no aliases are used, but when I use aliases for the 'barid' columns in the above example, I would expect that the provided aliases will be used as columnnames, but this is not how it works at the moment.
consider the following query:
SELECT a.barid as barid_foo, b.barid as barid_bar from foo a LEFT JOIN bar b ON a.barid = b.id;
Expected result (as in other database drivers):
a resultset with two columns named barid_foo and barid_bar.
result I get from the HXTT Access driver 4.0 :
a resultset with two columns named a.barid_foo, b.barid_bar
As we use the HXTT driver on one server and ms access JET drivers on our other servers, using the same server software, the results should be the same in both drivers. Is there a workaround for this behaviour, or is there an easy way to fix this?
Thank in advance,
Menno van Baalen
>Is there a workaround for this behaviour, or is there an easy way to fix this?
Checked. It has been supported when we supported the issue "Fields used in ON condition of JOIN have an explicit alias" (issued by Robbert Jan Grootjans, 2010-10-08 03:32:31.0):) So we needn't to do more work.
Please download the latest package.