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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
>
>
