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

From: Mike Kienenberger (mkienen..mail.com)
Date: Sat Aug 19 2006 - 20:03:02 EDT

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

    Here's an idea I had for handling splits.

    Right now, QueryAsembler.dbRelationshipAdded(..) removes "duplicates".
    If instead we temporarily "overrode" aliases when resolving a split
    expression, it seems as if this might work without any further
    changes.

    Going back to my standard example:

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

    We start off with an alias table

    FEE = t0
    FEE_TYPE = t1
    FEE_CYLE = t2

    Now we process an expression with a split

    AUTHORIZATION_DOCUMENT = t3
    PERMIT_DOCUMENT = t4

    the split expression ends, so we revert these two values.

    Next expression with a split gives us

    AUTHORIZATION_DOCUMENT = t5
    PERMIT_DOCUMENT = t6

    And these are again reverted in the alias table when the split expression ends.

    The reason I say reverted is that there may be regular inner joins
    (non-split) against these same tables. My example doesn't really
    show that off well.

    Assume there's another AUTHORIZATION_DOCUMENT.date = yyyy-mm-dd phrase
    that is an inner join. It already exists as an alias of
    AUTHORIZATION_DOCUMENT = t2Point5, so we want this to still be back in
    the alias table as soon as we are no longer processing the split.

    Hope this makes sense. I don't feel I'm explaining it very well.
    It's probably easier to implement than to explain, so I think this
    will be my next step.



    This archive was generated by hypermail 2.0.0 : Sat Aug 19 2006 - 20:03:26 EDT