I want to generate a join that looks something like:

FROM Customer
LEFT OUTER JOIN Order on Customer.CustomerId = Order.CustomerId and Order.OrderDate > 1/1/2005

In Oracle 9i this translates to:

FROM Customer, Order
WHERE Customer.CustomerId = Order.CustomerId(+)
AND Order.OrderDate (+)> 1/1/2005

When I add the predicate expression for the date I get:

FROM Customer, Order
WHERE Customer.CustomerId = Order.CustomerId(+)
AND Order.OrderDate > 1/1/2005

which only returns me customers that have orders in 2005, rather than all customers, with 2005 order details.

My example is rather more complex than this (and obviously the date syntax is incorrect) but this is my essential problem. Is setting ObeyWeakRelations going to have any effect on the predicate expression, or does that only affect the relation joins? Is there some other solution for this problem?

You've to set the CustomFilter property of a relation to the predicate you want to add.

Please see: using the generated code / selfservicing (adapter) / Filtering and sorting -> Custom filters for EntityRelations

I did as you suggested:

Dim customFilter As IPredicateExpression = New PredicateExpression
                customFilter.Add(PredicateFactory.CompareValue(SubprojectFieldIndex.ProjectId, ComparisonOperator.Equal, Me.Id, "CUST_SUBPROJECT"))

filterBucket.Relations.Add(MySubprojectCustomerEntity.Relations.SubprojectEntityUsingSubprojectId, "CUST_SUBPROJECT", JoinHint.Left).CustomFilter = customFilter

However the generated SQL still looks the same:

AND "CUST_SUBPROJECT"."SUBPROJECT_ID"(+)="ADPDS_IMPLEMENT_PORTAL"."SUBPROJECT_CUSTOMER"."SUBPROJECT_ID" 
AND ( "CUST_SUBPROJECT"."PROJECT_ID" = :ProjectId1) 

It should be :

AND "CUST_SUBPROJECT"."SUBPROJECT_ID"(+)="ADPDS_IMPLEMENT_PORTAL"."SUBPROJECT_CUSTOMER"."SUBPROJECT_ID" 
AND ( "CUST_SUBPROJECT"."PROJECT_ID" (+) = :ProjectId1) 

What am I missing?

You're not missing anything. I was under the assumption the code would do that, without checking it first myself, but it doesn't.

I've filed it as an issue to use non-ansi joins on 9i/10g still, it should be configurable (or automatic) to switch between non-ansi joins and ansi joins. Those wicked non-ansi joins are only used to re-use code for 8i too.

At the moment I have no other solution to this problem. I'm not sure if the where clause can be rewritten so it still gives all the customers, though I doubt it . I'll see if I can fix it via a setting today.

I've now added a .config appSetting: OracleAnsiJoins. omitting it or setting it to false, you'll get the current non-ansi joins. Setting it to true, you'll get ansi joins.

Builds for 10g have it set to true by default, so users of the 10g DQE don't have to set it to true to get ansi-joins.

This solves your problem Non-ansi joins are used for 8i.

I'll mail you a test archive to test it out.

Otis wrote:

I'll mail you a test archive to test it out.

It works. Thanks a bunch!

Ok!

It will be available (hopefully) tomorrow for all of you out there