Hi All,
I'm running into a strange problem that I can't seem to constrain to a
test case. I'm including the relevant pieces of the tables and
relationships as reference for the following description of the
problem. And, just for reference, I'm running SA from svn.
As you can see below, the Endorsement object is mapped against a
specific select statement on the endorsements table, with a set of
relationships to the EndorsementDefinition and EndorsementLineItem
objects. Endorsements themselves are children of Policy objects.
The actual ORA error I'm seeing is:
"(DatabaseError) ORA-01446: cannot select ROWID from view with
DISTINCT, GROUP BY, etc."
The select statement itself is too long to relate here, but the
relevant piece is as follows:
"ORDER BY distinct_ends_select.rowid,
uhe_endorsement_7775.end_endorsement_type ASC,
end_endorsement_3b96.end_endorsement_type ASC"
When I've run into 01446 before, I've merely added an explict order_by
to the relation, and this has taken care of it. But in this case,
I've added order_by in each relationship, but the SQL is still being
constructed with an attempt to order by rowid.
There is a "however" to this whole thing though, and it's a strange
one. If I build an Endorsement object explicitly, assigning all the
relevant primary keys and then running a session.import_instance
against the object, I get the above ORA error. However, if I just
allow the Policy object to lazyload the endorsement objects, it
retrieves them without any difficulty.
Because the relationships seem to work in lazyload, and becaue I've
already added all the "order_by" statements I can think to add, I'm
not sure where to go next, or how to narrow the whole of it down to a
test case. Any help, though, would be greatly appreciated. Thanks!
The above references the following set of tables and relationships:
policies = Table('pol_homepolicy_tbl', meta,
Column('pol_policy_num', String(10), primary_key=True, key='policyNum'),
Column('pol_policy_eff_date', DateTime, primary_key=True,
key='effectiveDate'),
# truncated
...
)
endorsements = Table('end_endorsement_tbl', meta,
Column('end_policy_num', String(10),
ForeignKey('pol_homepolicy_tbl.policyNum'), primary_key=True,
key='policyNum'),
Column('end_endorsement_type', String(20),
ForeignKey('uhe_endorsement_tbl.type'), primary_key=True, key='type'),
Column('end_endorsement_id', Integer, primary_key=True, key='id'),
Column('end_policy_eff_date', DateTime,
ForeignKey('pol_homepolicy_tbl.effectiveDate'), primary_key=True,
key='policyEffDate'),
Column('end_endorsement_edition_date', String(7),
ForeignKey('uhe_endorsement_tbl.editionDate'), key='editionDate'),
# truncated
...
)
endorsementDefinitions = Table('uhe_endorsement_tbl', meta,
Column('end_endorsement_type', String(10), primary_key=True, key='type'),
Column('uhe_effect_date', DateTime, primary_key=True, key='effectiveDate'),
Column('end_endorsement_desc', String(100), key='description'),
Column('end_endorsement_form_id', String(12), key='formNumber'),
Column('r_end_ed_date', String(7), key='editionDate')
)
EndorsementDefinition.mapper = mapper(EndorsementDefinition,
endorsementDefinitions)
EndorsementLineItem.mapper = mapper(EndorsementLineItem, endorsements)
distinct_ends_select = select(
[
endorsements.c.policyNum,
endorsements.c.policyEffDate,
endorsements.c.type,
endorsements.c.editionDate
],
order_by=[asc(endorsements.c.type)],
distinct=True,
).alias('distinct_ends_select')
Endorsement.mapper = mapper(Endorsement, distinct_ends_select,
is_primary=True, properties=dict(
definition = relation(EndorsementDefinition, lazy=False,
private=False, order_by=asc(endorsementDefinitions.c.type),
primaryjoin=and_(
distinct_ends_select.c.end_endorsement_type==endorsementDefinitions.c.type,
distinct_ends_select.c.end_endorsement_edition_date==endorsementDefinitions.c.editionDate,
),
),
lineItems = relation(EndorsementLineItem, lazy=False,
private=True, order_by=asc(endorsements.c.type),
foreignkey=(
endorsements.c.policyNum,
endorsements.c.policyEffDate,
endorsements.c.type,
),
primaryjoin=and_(
distinct_ends_select.c.end_policy_num==endorsements.c.policyNum,
distinct_ends_select.c.end_policy_eff_date==endorsements.c.policyEffDate,
distinct_ends_select.c.end_endorsement_type==endorsements.c.type
)
)
))
Policy.mapper = mapper(Policy, policies, extension=PolicyExtension(),
properties=dict(
endorsements = relation(Endorsement, lazy=True, private=True,
order_by=asc(distinct_ends_select.c.end_endorsement_type)),
# truncated
...
))
--
"All guilt is relative, loyalty counts, and never let your conscience
be your guide."
- Lucas Buck, American Gothic
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
