Looks like I'm on the right track for handling splits. My baseline
query now generates correctly. However, there's still a few bugs.
The second sql splits the joins correctly, but doesn't quite assign
the qualifier aliases correctly in the search expressions. This may
simply be a matter of timing when generating the search expression
sql.
I'm feeling a bit more confident about the whole process at this point.
SELECT * FROM (
SELECT t0.AMOUNT_DUE, t0.REQUIRED_DEPOSIT_AMOUNT_DUE, t0.FEE_ID, t0.FEE_TYPE_ID
FROM ENG_WORK_MGMT.FEE t0,
ENG_WORK_MGMT.FEE_TYPE t1,
ENG_WORK_MGMT.FEE_CYCLE t2,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
ENG_WORK_MGMT.PERMIT_DOCUMENT t6
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 t3.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
AND (
(t1.DESCRIPTION = ?)
AND (
(RTRIM(t4.IS_ACTIVE(+)) <> ?) OR (RTRIM(t6.IS_ACTIVE(+)) <> ?)
))) WHERE rownum <= 100 [bind: 'EDMS', 'Y', 'Y']
SELECT * FROM (
SELECT t0.AMOUNT_DUE, t0.REQUIRED_DEPOSIT_AMOUNT_DUE, t0.FEE_ID, t0.FEE_TYPE_ID
FROM
ENG_WORK_MGMT.FEE t0,
ENG_WORK_MGMT.FEE_TYPE t1,
ENG_WORK_MGMT.FEE_CYCLE t2,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t7,
ENG_WORK_MGMT.PERMIT_DOCUMENT t8,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t9,
ENG_WORK_MGMT.PERMIT_DOCUMENT t10
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 t3.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
AND t2.FEE_CYCLE_ID = t7.INITIAL_FEE_CYCLE_ID(+)
AND t3.AUTHORIZATION_DOCUMENT_ID = t8.PERMIT_DOCUMENT_ID(+)
AND t2.FEE_CYCLE_ID = t9.RECURRING_FEE_CYCLE_ID(+)
AND t3.AUTHORIZATION_DOCUMENT_ID = t10.PERMIT_DOCUMENT_ID(+)
AND (
(t1.DESCRIPTION = ?)
AND (
(RTRIM(t4.IS_ACTIVE(+)) <> ?) OR (RTRIM(t6.IS_ACTIVE(+)) <> ?)
)
AND (
(t4.AGENCY_ID = ?) OR (t4.AGENCY_ID = ?)
))) WHERE rownum <= 100 [bind: 'EDMS', 'Y', 'Y', 100, 100]
On 8/20/06, Mike Kienenberger <mkienen..mail.com> wrote:
> Yeah, I'm trying to see if I can find a short-term solution rather
> than having to reimplement everything all at once.
>
> I have everything working with the + notation now, except for the
> split and null issues.
> It looks like this will work to handle the split implementation, so
> "+" would mean both split and outer join in my use case. I will
> hopefully know sometime tomorrow :-)
>
> I think what you proposed is workable, but there's some unanswered
> questions, like how splits would be configured. I suppose it'd have
> to be done manually.
>
> I also need to convert from Oracle 8 notation to the generic notation.
> (Oracle 8 doesn't allow outer joins with OR), but I think this will
> be pretty trivial compared to what's been done so far.
>
> If I can get all of this working, then providing the EJB QL-compatible
> sytax down the road shouldn't be too difficult.
>
> On 8/20/06, Andrus Adamchik <andru..bjectstyle.org> wrote:
> >
> > On Aug 20, 2006, at 10:32 PM, Mike Kienenberger wrote:
> >
> > > I didn't state how a user would configure it, only how it could be
> > > processed if it was specified.
> >
> > Ah, ok.
> >
> > > Right now, I'm leaning on making
> > > join semantics represent this. So a query can be (inner|outer) +
> > > (split|nosplit). Realistically, that's probably only inner, inner
> > > w/split, and outer w/split as the three options.
> >
> > I guess that's JPA join semantics?
> >
> > > Your original idea of "+" and "|" works as part of the path expression
> > > works for me as well.
> >
> > I don't mind this as an alternative. I just thought it would be cool
> > if we find synergy between EJB QL and our current SelectQuery (and I
> > think we came close). If it turns out that we are stretching it too
> > much, we can use this semantics instead.
> >
> > Andrus
> >
> >
>
This archive was generated by hypermail 2.0.0 : Mon Aug 21 2006 - 18:04:37 EDT