Hi EveryBody

I'm so begginer with llblGen Pro ... Can AnyOne Help Me With this Query ? How Can I Generate This Query ?

SELECT * FROM Table1 a WHERE (Field1 = (SELECT MAX(Field1) FROM Table1 b WHERE b.KeyField = a.KeyField))

The query can be re-written as follows:

SELECT *
FROM Table1 a
WHERE (Field1 IN
(SELECT MAX(Field1)
FROM Table1 b
WHERE b.KeyField = a.KeyField))

And to implement the IN predicate you should use FieldCompareSetPredicate PLease refer to the LLBLGen Pro manual: "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> The predicate system"

Hi There Tanx For your Reply Still i have some problems ... I Dont know how to Create This Part : <<Where a.KeyField = b.KeyField>>

This is My Code

        EntityCollection currencyRate = new EntityCollection(new CurrencyRateEntityFactory());

        IEntityField2 setField = EntityFieldFactory.Create(CurrencyRateFieldIndex.ModifyDate);
        setField.AggregateFunctionToApply = AggregateFunction.Max;
        setField.ObjectAlias = "MD";

        IPredicate setFilter = PredicateFactory.CompareValue(CurrencyRateFieldIndex.CurrCode, ComparisonOperator.Equal, 1, "MD");

        RelationPredicateBucket filter = new RelationPredicateBucket();

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

        using (DataAccessAdapter adapter = new DataAccessAdapter())
        {
            adapter.FetchEntityCollection(currencyRate, filter, 0, null, pageIndex, 0);
        }

My Problem is How to Replace Constant Value "1" With Table1.CurrCode TNX

You need a field compare expression predicate, as described in Filtering and sorting in the manual. Please read that section of the manual as it's a very important one.

I'm not sure if you're using 1.0.2005.1 or v2.0, but either way, you can create predicates more easily using the operator overloading syntaxis. Please read the posting guidelines: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7722 so we get as much information as possible to answer your question better.

You should use the _entityname_Fields classes to create field objects, so you can create an a.Field = b.Field predicate by simplydoing: IPredicate p = (AFields.Field == bFields.Field);

Your code then is much cleaner

Your query is also inefficient, if I understand it correctly. What your query does is mainly fetching the entity from table1 which has the highest value for Field1.

This is the same as: SELECT TOP 1 * FROM Table1 ORDER BY Field1 DESC

This is much more efficient. This then leads to the code:

EntityCollection currencyRate = new EntityCollection(new CurrencyRateEntityFactory());
SortExpression sorter = new SortExpression();
sorter.Add(CurrencyRateFields.ModifyDate | SortOperator.Descending);

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(currencyRate, null, 1, sorter);
}

As you can see, the sortclause is created with the '|' operator and the CurrencyRateFields class, which is available to you in the HelperClasses namespace of the generated code.

If you really want to do it using your query, you should use the following code instead:

EntityCollection currencyRate = new EntityCollection(new CurrencyRateEntityFactory());
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Add(new FieldCompareSetPredicate(
    CurrencyRateFields.ModifyDate, null,
    CurrencyRateFields.ModifyDate.SetAggregateFunction(AggregateFunction.Max), null,
    SetOperator.Equal, 
    (CurrencyRateFields.ID==CurrencyRateFIelds.ID.SetObjectAlias("b"),
    null,
    "b",
    1,
    null, 
    false)));

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(currencyRate, filter);
}

As you can see, I use again the CurrencyRateFields class and use methods available on entityfield2 objects to set the aggregatefunction on a field and the alias. I alias only the subquery's field, not the normal query's field.

Great ... Thank You ... It Helps Me Alot ... Now It's Done !!!

I'm trying to do the same thing, but I'm not sure if the FieldCompareSetPredicate provides the necessary overloads to duplicate the following query:

SELECT Location.Location_Cd, Location.Descr, LH1.Parent_Location_Cd FROM Location LEFT OUTER JOIN Location_Hierarchy AS LH1 ON dbo.Location.Location_Cd = LH1.Child_Location_Cd WHERE LH1.Parent_Location_Cd = (SELECT TOP (1) Parent_Location_Cd FROM Location_Hierarchy AS LH2 WHERE Child_Location_Cd = LH1.Child_Location_Cd AND Effective_Dt <= GETDATE() ORDER BY Effective_Dt DESC) ORDER BY Location.Location_Cd

My query is joining a location table with a location_hieararchy table that maps one location to another. The catch is that for a given parent/child relationship between two locations, there is an Effective_Dt column that is used to determine the current parent of a given location. So the query requires me to not only have a predicate to link the subquery with the outer query using Child_Location_Cd, but also make sure that the max date is less than or equal to today.

It seems that the FieldCompareSetPredicate only allows for a single predicate to be placed when filtering the subquery. It would be nice if it would accept a Predicate Expression. That would give me what I need. Or is there another way to do this?

Can you please post this in a new thread and just use a link to this thread if you would like to use it as a reference?

Thank You.

coco wrote:

I'm trying to do the same thing, but I'm not sure if the FieldCompareSetPredicate provides the necessary overloads to duplicate the following query:

SELECT Location.Location_Cd, Location.Descr, LH1.Parent_Location_Cd FROM Location LEFT OUTER JOIN Location_Hierarchy AS LH1 ON dbo.Location.Location_Cd = LH1.Child_Location_Cd WHERE LH1.Parent_Location_Cd = (SELECT TOP (1) Parent_Location_Cd FROM Location_Hierarchy AS LH2 WHERE Child_Location_Cd = LH1.Child_Location_Cd AND Effective_Dt <= GETDATE() ORDER BY Effective_Dt DESC) ORDER BY Location.Location_Cd

My query is joining a location table with a location_hieararchy table that maps one location to another. The catch is that for a given parent/child relationship between two locations, there is an Effective_Dt column that is used to determine the current parent of a given location. So the query requires me to not only have a predicate to link the subquery with the outer query using Child_Location_Cd, but also make sure that the max date is less than or equal to today.

It seems that the FieldCompareSetPredicate only allows for a single predicate to be placed when filtering the subquery. It would be nice if it would accept a Predicate Expression. That would give me what I need. Or is there another way to do this?

Could you post what you have tried yourself which failed? (If possible with the query it produced )