Consider simple table:
CREATE TABLE t1 (ID int)
HXTT Access throws an error when the following query is executed:
SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
Invalid column: b.idjava.sql.SQLException: Invalid column: b.id
Error Code:213249
SQL State:S0022
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
...
Though if parentheses in the from-clause are ommited everything is fine:
SELECT * FROM t1 AS a INNER JOIN t1 AS b ON a.id=b.id WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
This is an example of a problem that I've met while trying to use HXTT Access with our existing database. Meanwhile Access accepts both queries equally well. Moreover, Access tends to put parentheses everywhere when views are constructed using its wizard.
So, of course we can fix particular problem query. But this is not acceptible solution as there is no guarantee that this problem will not arise with new views.
Is there any possibility that you fix that problem in HXTT Access?
Thanks in advance,
Nikita
|
>SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
>Invalid column: b.idjava.sql.SQLException: Invalid column: b.id
>Though if parentheses in the from-clause are ommited everything is fine:
With/without () is used to decided whether all columns in a join table is visible/invisible for outside sql. For instance, SELECT * FROM (t1 AS a INNER JOIN t1 AS b ON a.id=b.id) as ab WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = ab.id) will work.
|
First of all, it is not clear how to refer the field id of table b and not table a. In the example above there is no problem as a.id=b.id. But how to rewrite query if we change <> to =?
SELECT *
FROM (t1 AS a INNER JOIN t1 AS b ON a.id<>b.id)
WHERE EXISTS (SELECT * FROM t1 c WHERE c.id = b.id)
Secondly, () are used in Access to set the order of joining and it doesn't affect visibility.
So, in queries like:
SELECT * FROM (t1 INNER JOIN t2 ON t1.id=t2.id) INNER JOIN t3 ON t1.id=t3.id
we can't simply omit parentheses as Access will not accept such query.
So, it appears that views defined in Access and correct for Access are not compatible with HXTT ACCESS. Right?
|
v2.1.068 changed the visibility of columns in JOIN table with parentheses from invisible to visible.
|
Indeed, the problem is fixed. Thank you!
PS: it seems that you have banned my ip: 83.219.159.2 from visiting
http://www.hxtt.com/support_view_issue.jsp
I'm sorry, if my posts seemed offensive to you. Hope you remove the ban
so we can continue evaluating your product for the needs of our company.
|
Checked, and removed your IP from spam list. Because some robots submit always rubbish messages to our support site, so that our web has to filtered it automatically.
|