Hello,

I have an oracle 10g table containing three fields: ID, Name and Version.

I would like to select all the latest version IDs.

for instance, if I had these rows:

• 1, "CostumerA", 1.0
• 2, "CostumerA", 2.0
• 3, "CostumerB", 3.0 the result would be: 2, 3

The generated statement should be something like this:

SELECT ID
FROM VersionedCostumer VCROOT
WHERE VCROOT.Version = (
                                            SELECT MAX(Version)
                                            FROM VersionedCostumer
                                            WHERE Name = VCROOT.Name )

How do I create a suitable filter (Assuming I have the latest LLBLGen version) ?

C# is preferable.

Thanks in advance, Roy Richter.

What have you tried yourself, which failed? Perhaps it's a small thing I can fix for you.

Hi Otis,

• First of all, i would like to commend you and everyone responsible for LLBLGen on a great developer tool. The company I am currently working for plans to buy LLBLGen licenses ASAP ( My recommendation of course :] ). My code is definitely flawed, but here it is:

IEntityField2 setField = EntityFieldFactory.Create(BlockVersionFieldIndex.Version);
setField.AggregateFunctionToApply = AggregateFunction.Max;

RelationPredicateBucket setFilter = new RelationPredicateBucket();
setFilter.PredicateExpression.Add(PredicateFactory.CompareValue(BlockVersionFieldIndex.Name, ComparisonOperator.Equal, @"""GIS"".""BLOCK_VERSION"".""NAME""", "BV"));

RelationPredicateBucket filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(BlockVersionFieldIndex.Version), null,
    setField, null,
    SetOperator.Equal,
    setFilter.PredicateExpression,
    setFilter.Relations,
    string.Empty,
    0, 
    null,
    false,
    null));


EntityCollection blocks = new EntityCollection(new BlockVersionEntityFactory());
mAdapter.FetchEntityCollection(blocks, filter, -1);

And the result is:

 SELECT "GIS"."BLOCK_VERSION"."MSLINK" AS "Mslink","GIS"."BLOCK_VERSION"."NAME" AS "Name","GIS"."BLOCK_VERSION"."VERSION" AS "Version"
FROM "GIS"."BLOCK_VERSION"
WHERE ( "GIS"."BLOCK_VERSION"."VERSION" = (

SELECT MAX("GIS"."BLOCK_VERSION"."VERSION") AS "Version"
FROM "GIS"."BLOCK_VERSION"
WHERE ( "BV"."NAME" = "GIS"."BLOCK_VERSION"."NAME")))

One small question - Is it possible to alias the scheme (GIS) and table names(BLOCK_VERSION)?

Thanks,

you should alias the table in the subquery.

Please see this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3401

So your code will become: [code]

IEntityField2 setField = EntityFieldFactory.Create(BlockVersionFieldIndex.Version);
setField.AggregateFunctionToApply = AggregateFunction.Max;
setField.ObjectAlias = "VC";

// bucket is not necessary here, 
//RelationPredicateBucket setFilter = new RelationPredicateBucket();
IPredicate setFilter = PredicateFactory.CompareValue(BlockVersionFieldIndex.Name, ComparisonOperator.Equal, @"""GIS"".""BLOCK_VERSION"".""NAME""", "BV", "VC");

RelationPredicateBucket filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(BlockVersionFieldIndex.Version), null,
    setField, null,
    SetOperator.Equal,
    new PredicateExpression(setFilter),
    null,
    string.Empty,
    0, 
    null,
    false,
    null));


EntityCollection blocks = new EntityCollection(new BlockVersionEntityFactory());
mAdapter.FetchEntityCollection(blocks, filter, 0);

-> 2 times an alias is specified, once for the setfield and once in the comparison for the setfilter. I've removed the setfilterbucket, as that's not necessary here, and I've changed -1 to 0, as 0 is defined as 'give all rows'.

* First of all, i would like to commend you and everyone responsible for LLBLGen on a great developer tool. The company I am currently working for plans to buy LLBLGen licenses ASAP ( My recommendation of course :] ).