I'm aware that subqueries are not implemented yet or that they can be converted to a join, but I'm not sure if I can do it with this query. I was thinking dynamic lists, but I'm stuck when I try to set the relation collection because it needs to use PKs. (Selfservicing)

lot is a historic table that contains...well lot information. What the query does is it selects the latest information (max(cdate)) for specific lots.

select autoid, code, cdate, etc, etc 
from lot, (select max(cdate) as maxcdate, code from lot where code IN ('a01', 'a02', 'a03') group by code) as mx 
WHERE lot.code=mx.code AND maxcdate=lot.cdate

Could someone point me in the right direction, or flat out tell me it can't be done?

Thanks!

Cour wrote:

I'm aware that subqueries are not implemented yet or that they can be converted to a join, but I'm not sure if I can do it with this query. I was thinking dynamic lists, but I'm stuck when I try to set the relation collection because it needs to use PKs. (Selfservicing) lot is a historic table that contains...well lot information. What the query does is it selects the latest information (max(cdate)) for specific lots.

select autoid, code, cdate, etc, etc 
from lot, (select max(cdate) as maxcdate, code from lot where code IN ('a01', 'a02', 'a03') group by code) as mx 
WHERE lot.code=mx.code AND maxcdate=lot.cdate

Could someone point me in the right direction, or flat out tell me it can't be done?

Subqueries are supported, (fieldCompareSetPredicate) but only in where clauses, not in from clauses. So your query, which uses a dynamic temp table (there is a better word for it though) in the form of a subquery is not yet supported.

The query can be converted to a subquery using WHERE clause but that's perhaps a lot slower (as the RDBMS can easily optimize your version), but at the moment (will change soon) the fieldcomparesetpredicate doesn't accept a groupby clause.