In english, I want a list of trades in a portfolio, whether tradeprices exist or not... but if they exist, they should be dated at least 7/1/2005.

In oracle, it looks like:
from trade t, tradeprice tp where t.portfolio=53 and tp.tradeid(+)=t.tradeid and tp.effdate(+) >= '1-Jul-2005'

In ansi, it looks like:
from trade t left outer join tradeprice tp on t.tradeid=tp.tradeid and tp.effdate >= '1-Jul-2005' where t.portfolio=53

In HQL, the closest I can get is:
from trade t left join fetch t.tradeprices tp where t.portfolio=53 and tp.effdate >= '1-Jul-2005'

But that HQL generates this incorrect SQL:
from trade t left outer join tradeprice tp on t.tradeid=tp.tradeid where t.portfolio=53 and tp.effdate >= '1-Jul-2005'

This SQL is not correct because the effdate condition needs to be in the "on" clause, not the "where" clause. But the "on" clause is generated internally by NHibernate, and I haven't found a way to alter it. Is there a way?

btw... tradeprice is a lazy init bag on trade object

I see in Hibernate3 there is a "with" clause, but it does not appear to work with NHibernate. Any ideas how to get around this limitation in the meantime?

Thanks!!!
-Michael

I am having the same issue. Has this been resolved yet?

Since I originally opened this issue 3 years ago, I've found a few workarounds... 1) use named queries with SQL having any join syntax desired... 2) use all oracle outer join syntax in HQL with no mix of ansi join syntax... 3) simply remove the extra condition in the HQL, which would return more rows than desired, then manually eliminate unwanted rows in the business logic.