Re: Idea for handling splits, possibly with only minor changes to alias table handling

From: Mike Kienenberger (mkienen..mail.com)
Date: Mon Aug 21 2006 - 18:04:13 EDT

  • Next message: Mike Kienenberger: "Re: Nulls in oracle (ie, empty outer join records) cannot be compared"

    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