I have encountered a situation where OJB at different times generates
two different SQL queries from the same Criteria object, I guess
according to the order of some (unordered) collection.
My repository is like this:
<!-- A is the top-level object. It has a reference bRef, -->
<!-- which contains either a BSub1 or a BSub2, which are the -->
<!-- two subclasses of BSuper -->
<class-descriptor class="A">
<field-descriptor name="aKey" column="A_KEY" primarykey="true" />
<field-descriptor name="bKey" column="B_KEY" />
<field-descriptor name="data" column="DATA" />
<reference-descriptor name="bRef"
class-ref="BSuper">
<foreign-key field-ref="bKey"/>
</reference-descriptor>
</class-descriptor>
<!-- BSuper is the superclass of the two possible B classes -->
<class-descriptor class="BSuper">
<extent-class class-ref="BSub1" />
<extent-class class-ref="BSub2" />
<field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<reference-descriptor name="cRef"
class-ref="C">
<foreign-key field-ref="cKey"/>
</reference-descriptor>
</class-descriptor>
<!-- BSub1 is one of the possible B classes -->
<class-descriptor class="BSub1">
<field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<field-descriptor name="cKey" column="C_KEY" />
<reference-descriptor name="cRef"
class-ref="C">
<foreign-key field-ref="cKey"/>
</reference-descriptor>
</class-descriptor>
<!-- BSub2 is one of the possible B classes -->
<class-descriptor class="BSub2">
<field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<field-descriptor name="cKey" column="C_KEY" />
<reference-descriptor name="cRef"
class-ref="C">
<foreign-key field-ref="cKey"/>
</reference-descriptor>
</class-descriptor>
<!-- Both BSub1 and BSub2 have a reference to a C -->
<class-descriptor class="C">
<field-descriptor name="cKey" column="C_KEY" primarykey="true" />
<field-descriptor name="value" column="VALUE" />
</class-descriptor>
Given this setup, I now want to write a Report Query to retrieve
A.data and C.value, and only for certain values of C.value. I
don't care whether I got to C via BSub1 or via BSub2. So I use
Criteria.addIn("bRef.cRef.value", values).
The SQL which is generated for my Report Query is non-deterministic.
Sometimes it looks like this:
SELECT A.DATA, C1.VALUE FROM A, B1, C C1, B2, C C2
WHERE A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C1.C_KEY(+)
AND A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C2.C_KEY(+)
AND ((C1.VALUE IN (...)) OR (C2.VALUE IN (...)));
and sometimes it looks like this:
SELECT A.DATA, C2.VALUE FROM A, B1, C C1, B2, C C2
WHERE A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C1.C_KEY(+)
AND A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C2.C_KEY(+)
AND ((C1.VALUE IN (...)) OR (C2.VALUE IN (...)));
What it should look like is this:
SELECT A.DATA, C.VALUE FROM A, B1, B2, C
WHERE ((A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C.C_KEY)
OR (A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C.C_KEY);
AND C.VALUE IN (...);
(I think ... I'm not so great with outer joins, so I'm not positive on
this one). As a result of this problem, sometimes I get valid data
from my query and sometimes all of the C.VALUEs are null, because I
get values that match on one of the two routes to C but the values
that are actually returned are from the other branch, where the outer
join supplies NULL for the (otherwise non-matching) rows.
I'm using CVS HEAD as of yesterday. Any insights?
thanks,
--
Steve Clark
Technology Applications Team
Natural Resources Research Center/USGS
[EMAIL PROTECTED]
(970)226-9291
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
