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? > >> > > >> > > > >
