Ok. It looks like the problem is this.
SelectTranslator.dbRelationshipAdded only creates one table alias
(FROM table entry) for a particular relationship's target entity.
That works great for INNER joins, but for LEFT OUTER joins, I think we
need one per source/target pair. Unfortunately, I don't see a
backwards-compatible way to pass the join semantics to this public
method. Unless we can set join semantics directly on the
relationship itself. Maybe it should be an attribute of DbJoin and
we could pull it up that way. It's getting late and I'm not really
thinking clearly any more, but I'd appreciate any input for when I
start up again on it tomorrow.
public void SelectTranslator.dbRelationshipAdded(DbRelationship rel) {
[...]
String existAlias = (String) aliasLookup.get(rel);
if (existAlias == null) {
dbRelList.add(rel);
// add alias for the destination table of the relationship
String newAlias = newAliasForTable((DbEntity)
rel.getTargetEntity());
aliasLookup.put(rel, newAlias);
}
}
When it's an outer join, then each outer join needs to be given a
unique alias entry, but if it's an inner join, we only want one entry.
On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
SELECT * FROM (SELECT [...] FROM
ENG_WORK_MGMT.FEE t0,
ENG_WORK_MGMT.FEE_TYPE t1,
ENG_WORK_MGMT.FEE_CYCLE
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
WHERE
t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
AND t0.FEE_ID = t2.FEE_CYCLE_ID
AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
AND ((t1.DESCRIPTION = ?)
AND ((t4.AGENCY_ID = ?)
OR (t4.AGENCY_ID = ?))))
Ok. I appear to be having some kind of unwanted optimization occurring.
There should be a line that says
AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
or maybe even
ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
with (t6.AGENCY_ID = ?)
On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> Ok. I think I have Oracle8 style outer joins working. I'll take a
> shot at the other ones tomorrow.
>
> On 8/17/06, Andrus Adamchik <[EMAIL PROTECTED]> wrote:
> > Yes, actually there was some discussion before to use such syntax for
> > the inner joins as well. I am all for it (I guess we have to preserve
> > a backdoor for the old syntax in case some db does not support such
> > syntax).
> >
> > Andrus
> >
> > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> >
> > > Even better link
> > >
> > > http://www.devx.com/dbzone/Article/17403/0/page/3
> > >
> > > Looks like we do away with WHERE clause joins altogether (at least for
> > > Oracle) and explicly join everything with ON statements.
> > >
> > > On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> > >> This is somewhat helpful for the various kinds of joins.
> > >>
> > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> > >>
> > >> Still looking for complex examples.
> > >>
> > >> On 8/17/06, Mike Kienenberger <[EMAIL PROTECTED]> wrote:
> > >> > On 8/17/06, Andrus Adamchik <[EMAIL PROTECTED]> wrote:
> > >> > > It would be nice if we could implement the translator using
> > >> standard
> > >> > > SQL syntax ("left outer join" instead of "(+)"), as it will
> > >> work on
> > >> > > most DB's including Oracle (starting from 9i), while the "(+)"
> > >> syntax
> > >> > > only works on Oracle (and is probably considered legacy syntax by
> > >> > > Oracle too).
> > >> > >
> > >> > > select
> > >> > > name,
> > >> > > department_name
> > >> > > from
> > >> > > employees e
> > >> > > left outer join
> > >> > > departments d
> > >> > > on
> > >> > > e.department_id = d.department_id;
> > >> > >
> > >> > > It will be somewhat harder to implement, but will solve the issue
> > >> > > once and for all.
> > >> >
> > >> > Well, sure, now you tell me :-)
> > >> >
> > >> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
> > >> > better choice :-)
> > >> >
> > >> > I guess I need to see if I can find some documentation on this
> > >> format.
> > >> >
> > >> > The simple example is obvious, but what does it look like with more
> > >> > tables involved, some with more outer joins and some without?
> > >> >
> > >>
> > >
> >
> >
>
