OUTER JOIN with additional field criteria on the join specification

[uhurusurfa]

I realize there is a solution to this as shown in this post:
http://forums.smartclient.com/forum/...=1486158589561

However this can result in an un-optimised query so was wondering if there have been any enhancements to enable specifying additional restrictions on the outer join table instead of having to add criteria that ends up in the WHERE clause .

I would like to be able to restrict the joined records by one or more fields in the foreign keys specified datasource. The most simple example would be to include only active records.

I have been searching high and low in the forums and the documentation but cannot see anything obvious.
I was thinking there might be a feature to add additional fields to include in the join spec both programmatically and in the XML defined datasources on the field declared as a foreign key.
Anyone got some pointers on this?

[Blama]

Hi uhurusurfa

I'm pretty sure this is not possible.

Regarding the un-optimised query: Are you sure about this? See eg here, when Oracle introduced ANSI syntax years ago.
I'd compare execution plans for "JOIN + WHERE" and "JOIN with 2 conditions". I'm almost sure Oracle (or any other RDBMS) will optimize the query, so that the result is the same.

Best regards
Blama

[uhurusurfa]

Thanks Blama for the feedback. My experience with MySQL is that the optimizer can make the wrong decisions when record counts get significantly high in join tables and is most often corrected by simply moving the conditions into the JOIN clause (the MySQL optimizer changes it execution plan at some threshold of record count in the different join tables).

However, I am aware there are ways to work around this in Smartclient using Velocity though I would have preferred to avoid that if possible.

It appears only Velocity scripting provides access to the separate clauses of the SQL statement ($defaultSelectClause, $defaultTableClause and $defaultWhereClause) . I do not see any mechanism provided in the Javadocs for equivalent access via Java (would have thought there would be a method like getDefaultTableClause() in the DataSource) ...if there is I would appreciate if you could direct me to the docs or explain how. The other scripting languages do not seem to have access either as shown here : http://www.smartclient.com/smartclie...Binding.script

[Isomorphic]

See SQLDataSource.getSQLClause() for clause-by-clause access from Java.