I have an issue with applying ordering to a ReadAllQuery where an outer join is created by using getAllowingNull() and the object referenced has an inheritance policy.
Currently the query produced looks like:
SELECT t0.OBJECTID FROM OBJECT t0, USER t2, ENTITY t1 WHERE ((((t0.DELETED = 'F')) AND ((t1.ENTITYID (+) = t0.RECEIVEDBY) AND ((t2.ENTITYID (+) = t1.ENTITYID) AND (t1.CLASSINDICATOR = 1)))) ORDER BY DECODE(t1.ENTITYID, 1234, 'Last, First','') DESC, t0.OBJECTID DESC
This does not include results where t0.RECEIVEDBY is null.
The documentation for oracle states ( Joins ): "or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause."
I have experimented with the query against our oracle database and found that the following query returns the results I want:
SELECT t0.OBJECTID FROM OBJECT t0, USER t2, ENTITY t1 WHERE ((((t0.DELETED = 'F')) AND ((t1.ENTITYID (+) = t0.RECEIVEDBY) AND ((t2.ENTITYID (+) = t1.ENTITYID) AND (t1.CLASSINDICATOR *(+)* = 1)))) ORDER BY DECODE(t1.ENTITYID, 1234, 'Last, First','') DESC, t0.OBJECTID DESC
So my question is, how can I tell toplink that I need the CLASSINDICATOR column to participate in the left join?
Here are the relevant portions of my descriptors.
InheritancePolicy inheritancePolicy = descriptor.getInheritancePolicy();
My attempt to force the left join is done via:
The += syntax for outer joins in obsolete, the SQL standard is to declare the outer join in the FROM clause. EclipseLink has used the FROM clause for some time, it seems you are on an old version.
If you cannot upgrade, you may be able to change your platform to use the FROM clause outer join syntax, or set setAlwaysUseOuterJoinForClassType(true) in InheritancePolicy.
You sir, are a Gentleman and a scholar. Thank you for taking time to answer my question. I had previously known about setAlwaysUseOuterJoinForClassType(true) but it continued to not work as desired. The secret was to use:
for both descriptors that used the inheritance policy.