Bugs item #518617, was opened at 2002-02-16 15:55
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=518617&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Open
>Resolution: Accepted
Priority: 5
Submitted By: Thomas Hamann (thomash76)
>Assigned to: Dain Sundstrom (dsundstrom)
Summary: unfavorable query composition
Initial Comment:
win2k, jdk1.3.1_001
Hi,
when the following is specified in jbosscmp-jdbc.xml...
<declared-sql>
<from>t, TITLE tt</from>
<where>tt.locale_column={0} and tt.uid_column=t.uid_column</where>
<order>title_column</order>
</declared-sql>
... for a bean with table 'TEMPLATE', JBoss generates:
SELECT TEMPLATE.uid_column FROM TEMPLATE t, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=t.uid_column ORDER BY title_column
unfortunately Oracle8 does not accept this statement, because it does not manage to
match
'TEMPLATE' (in select clause) with 't' (in where clause).
when writing the above query manually, it is most likely to be:
SELECT t.uid_column FROM TEMPLATE t, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=t.uid_column ORDER BY title_column
which will be accepted/and executed by Oracle.
i suggest to insert the alias (if specified) of a table into the select clause of the
query instead of
using the original table name. so a modification of the query composer would be
necessary...
however, there is a workaround for this bug - just avoid to use an alias for the table
of a bean and
additionally prepend that table name to the corresponding columns in the where clause,
like that:
SELECT TEMPLATE.uid_column FROM TEMPLATE, TITLE tt WHERE tt.locale_column=? and
tt.uid_column=TEMPLATE.uid_column ORDER BY title_column
i believe that this workaround is suitable for simple queries only. i expect ambiguity
exceptions
(raised by Oracle) when using the same table several times in a query without and with
aliases in
parallel...
Thomas
----------------------------------------------------------------------
You can respond by visiting:
http://sourceforge.net/tracker/?func=detail&atid=376685&aid=518617&group_id=22866
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
