Hi,
I'd like to know if OJB is able to generate a particular query, and if so,
how?
This is the case:
I have two classes; a class Container and a class AbstractAttribute (with
fields NAME and VALUE).
The class Container has a collection of AbstractAttribute's (fk relation),
every AbstractAttribute is related to one and only one Container.
So suppose a want to find all Container's where:
abstract attribute NAME = 'xxx' and abstract attribute VALUE = 'aaaa'
and abstract attribute NAME = 'yyy' and abstract attribute VALUE = 'bbbb'
ordered by abstract attribute VALUE where NAME = 'zzzz'
The actual generated query should be something like:
SELECT DISTINCT A0.ID, A0.NUMBER,A1.VALUE
FROM CONTAINER A0 INNER JOIN ABSTRACT_ATTRIBUTE A1 ON A0.ID=A1.OID
INNER JOIN ABSTRACT_ATTRIBUTE A2 ON A0.ID=A2.OID
INNER JOIN ABSTRACT_ATTRIBUTE A3 ON A0.ID=A3.OID
WHERE ( A1.NAME = 'zzzz' ) AND
(( A2.NAME = 'xxx' ) AND (A2.VALUE = 'aaaa' )) AND
(( A3.NAME = 'yyy' ) AND (A3.VALUE = 'bbbb' ))
ORDER BY 3
However, all that i've tried resulted in queries like:
SELECT DISTINCT A0.ID, A0.NUMBER,A1.VALUE
FROM CONTAINER A0 INNER JOIN ABSTRACT_ATTRIBUTE A1 ON A0.ID=A1.OID
WHERE ( A1.NAME = 'zzzz' ) AND
(( A1.NAME = 'xxx' ) AND (A1.VALUE = 'aaaa' )) AND
(( A1.NAME = 'yyy' ) AND (A1.VALUE = 'bbbb' ))
ORDER BY 3
The actual generated query always results in an empty resultset, an field
obviously cannot hold the value 'aaaa' and 'bbbb' at the same time!
How can i force OJB to generate the correct query?
Currently i'm using 0.9.7 (we will migrate to a newer version if that's
stable
later on) using HSQL 1.7.1, the given query works on the HSQL.
Roger Janssen mailto:[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
iBanx B.V.
*************************************************************************
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed.You should not copy, disclose or distribute this communication
without the authority of iBanx bv. iBanx bv is neither liable for
the proper and complete transmission of the information has been maintained
nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
