Hi,

Can you give me some tips how to create a filter and relation equivalent to this query:

SELECT a.* FROM sometable a, ( SELECT column1, MAX(column2) as column2 FROM sometable GROUP BY column1 ) b WHERE a.column1 = b.column1 AND a.column2 = b.column2

I want to have the result on SomeTableEntity or EntityCollection<SomeTableEntity>.

I'm using version 2.6

Thanks.

This is how you can do that: Please read the Derived tables and dynamic relations - manual section.

Thanks for the link.

I'm still stock how to do it using Dynamic Relation and Derived Tables. On my query, I need to join 2 fields to the derived table, but when I create instance of the DynamicRelation, it only accept one IPredicate. Also I want to ask if this join clause will be supported on Oracle 8i which doesn't support ansi-join.

Here's the code that I created.

                ResultsetFields fields = new ResultsetFields(2);
                fields.DefineField(SomeTableFields.Column1, 0);
                fields.DefineField(SomeTableFields.Column2, 1, "Column2", AggregateFunction.Max);
                GroupByCollection groupBy = new GroupByCollection(fields[0]);
                IPredicateExpression filter = new PredicateExpression(SomeTableFields.Column1 == <somevalue>);
                DerivedTableDefinition deriveTable = new DerivedTableDefinition(fields, "a", filter, groupBy, <im stock in here>);

            
                RelationPredicateBucket filter2 = new RelationPredicateBucket();


                filter2.Relations.Add(relation);
                filter2.SelectListAlias = "b";
                filter2.PredicateExpression.Add(new EntityField2("Column1", "a", typeof(int)) == new EntityField2("Column1", "b", typeof(int)));
                filter2.PredicateExpression.Add(new EntityField2("Column2", "a", typeof(int)) == new EntityField2("Column2", "b", typeof(int)));

                EntityCollection<SomeTableEntity> results = new EntityCollection<SomeTableEntity>();
                adapter.FetchEntityCollection(results , filter2);

 return results;

SELECT a.* 
FROM sometable a,
(
SELECT column1,
    MAX(column2) as column2
FROM sometable
WHERE column1 = <somevalue>
GROUP BY column1
) b
WHERE a.column1 = b.column1
AND a.column2 = b.column2

LLBLGen 2.6 DotNet 2.0 Oracle 8i

Please ignore the code on my previous post, my code should looks like this :

                ResultsetFields fields = new ResultsetFields(2);
                fields.DefineField(SomeTableFields.Column1, 0);
                fields.DefineField(SomeTableFields.Column2, 1, "Column2", AggregateFunction.Max);
                GroupByCollection groupBy = new GroupByCollection(fields[0]);
                IPredicateExpression filter = new PredicateExpression(SomeTableFields.Column1 == <somevalue>);
                DerivedTableDefinition deriveTable = new DerivedTableDefinition(fields, "a", filter, groupBy);


                DynamicRelation relation = new DynamicRelation(deriveTable, JoinHint.Inner, EntityType.ContextsetEntity, "b", <im stock in here>);

            
                RelationPredicateBucket filter2 = new RelationPredicateBucket();



                filter2.Relations.Add(relation);
                filter2.SelectListAlias = "b";
                filter2.PredicateExpression.Add(new EntityField2("Column1", "a", typeof(int)) == new EntityField2("Column1", "b", typeof(int)));
                filter2.PredicateExpression.Add(new EntityField2("Column2", "a", typeof(int)) == new EntityField2("Column2", "b", typeof(int)));

                EntityCollection<SomeTableEntity> results = new EntityCollection<SomeTableEntity>();
                adapter.FetchEntityCollection(results , filter2);

               return results;

I'm a bit puzzled about why you need the

IPredicateExpression filter = new PredicateExpression(SomeTableFields.Column1 == <somevalue>);

in your derived table definition - your original SQL query that you wanted to get does not contain it.

You are successfully joining the derived table to the outer part of the query using the

filter2.PredicateExpression.Add(new EntityField2("Column1", "a", typeof(int)) == new EntityField2("Column1", "b", typeof(int)));
                filter2.PredicateExpression.Add(new EntityField2("Column2", "a", typeof(int)) == new EntityField2("Column2", "b", typeof(int)));

so should not need the filter on the derived table ?

Sorry for the confusion. On my first post, there is no filter for the derived table

SELECT a.* 
FROM sometable a,
(
SELECT column1,
    MAX(column2) as column2
FROM sometable
GROUP BY column1
) b
WHERE a.column1 = b.column1
AND a.column2 = b.column2

But my actual requirements is there will be additional parameter so I modified my original query (See on my second posting). I think this parameter can also be implemented on the RelationPredicateBucket with same result.

SELECT a.* 
FROM sometable a,
(
SELECT column1,
    MAX(column2) as column2
FROM sometable
WHERE column1 = <somevalue>
GROUP BY column1
) b
WHERE a.column1 = b.column1
AND a.column2 = b.column2

Your suggestion give me some idea. I will try to pass one joining field on the IPredicate onClause parameter of DynamicRelation constructor and then I will put the other joining field on the RelationPredicateBucket.

OK, let us know how you get on

Thanks for the suggestion. I'm able to solve my problem using the following code:

       public static SomeTableEntity Get(decimal id)
        {
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {

                ResultsetFields fields = new ResultsetFields(2);
                fields.DefineField(SomeTableFields.Column1, 0);
                fields.DefineField(SomeTableFields.Column2, 1, "Column2", AggregateFunction.Max);
                GroupByCollection groupBy = new GroupByCollection(fields[0]);
                IPredicateExpression filter = new PredicateExpression(SomeTableFields.Column1 == id);
                DerivedTableDefinition deriveTable = new DerivedTableDefinition(fields, "a", filter, groupBy);

                RelationPredicateBucket filter2 = new RelationPredicateBucket();

                DynamicRelation relation = new DynamicRelation(deriveTable, JoinHint.Inner, EntityType.SomeTableEntity, "b",
                    (new EntityField2(SomeTableFieldIndex.Column1.ToString(), "a", typeof(int)) ==
                           SomeTableFields.Column1.SetObjectAlias("b")));


                filter2.Relations.Add(relation);
                filter2.SelectListAlias = "b";
                filter2.PredicateExpression.Add(new EntityField2(fields[1]).SetObjectAlias("a") == SomeTableFields.Column2.SetObjectAlias("b"));


                EntityCollection<SomeTableEntity> someTables = new EntityCollection<SomeTableEntity>();
                adapter.FetchEntityCollection(someTables, filter2);

                return someTables.Count > 0 ? someTables[0] : null;

            }
        }

Thanks for the help.