Skip to Main Content

Integration

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple aliases to the same table in a query?

446932Aug 31 2005 — edited Sep 1 2005
I need to know how to make multiple table aliases to the same table using the query expression framework.

Basically, we have the following:

object ARTIFACT has a collection of ATTRIBUTE_VALUE objects
ATTRIBUTE_VALUE has a value and a reference to an ATTRIBUTE_DEF object
ATTRIBUTE_DEF has a name and a type.


So, I could have an ARTIFACT that has two ATTRIBTE_VALUES, one for the "string1" ATTRIBUTE_DEF, one for the "string2" ATTRIBUTE_DEF.

Let's say I'm querying for artifacts where the "string1" attribute's value is "foo" and the "string2" attribute's value is "bar":

When I perform a query, TopLink generates the following SQL:

SELECT A.*
FROM ARTIFACT A, ATTRIBUTE_VALUE V
WHERE
(
V.VALUE="foo" and V.ATTRIBUTE_DEF_ID=12 // id for "string1"
)
AND
(
V.VALUE="bar" and V.ATTRIBUTE_DEF_ID=13 // id for "string2"
)
AND
(
V.ARTIFACT_ID=A.ID
)

This is obviously not going to work. What I need it to do is the following:

SELECT A.*
FROM ARTIFACT A, ATTRIBUTE_VALUE V1, ATTRIBUTE_VALUE V2
WHERE
(
V1.VALUE="foo" and V1.ATTRIBUTE_DEF_ID=12 AND V1.ARTIFACT_ID=A.ID
)
AND
(
V2.VALUE="bar" and V2.ATTRIBUTE_DEF_ID=13 AND V2.ARTIFACT_ID=A.ID
)


I can't figure out how to set table aliases in the ExpressionBuilder framework. Is this possible?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2005
Added on Aug 31 2005
4 comments
1,006 views