can any body help me out and tell,how to implements this Sql query in llbl. These are two tables 1.customerItempriceSchema 2.customeritempriceschemaentries

basically i want to have filter nested inside filter as select in this query

SELECT VirtualBroker.customerItemPriceSchema.expirationDate, VirtualBroker.customerItemPriceSchemaEntries.greaterThan FROM VirtualBroker.customerItemPriceSchema INNER JOIN VirtualBroker.customerItemPriceSchemaEntries ON VirtualBroker.customerItemPriceSchema.id = VirtualBroker.customerItemPriceSchemaEntries.customerItemPriceSchema WHERE (VirtualBroker.customerItemPriceSchema.expirationDate = (SELECT MAX(expirationDate) AS Expr1 FROM VirtualBroker.customerItemPriceSchema AS customerItemPriceSchema_1 WHERE (customerItemID = 10)))

Using something like this would give you the schema you were looking for. If the relationship already exists between the schema and entries then you should be able to the entity that is returned to access all of the entries that join to it. Let me know if this helps.

 IPredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(CustomerItemPriceSchemaFieldIndex.CustomerItemID, ComparisonOperator.Equal, 10);
ISortExpression sort = new SortExpression(SortClauseFactory.Create(CustomerItemPriceSchemaFieldIndex.ExpirationDate, SortOperator.Descending));
CustomerItemPriceSchemaColletion custs = new CustomerItemPriceSchemaColletion();
custs.GetMulti(filter, 1, sort);
if(custs.Count > 0)
{
    // Do whatever you needed too.
}

Gourav wrote:

basically i want to have filter nested inside filter as select in this query

Take a look at this page in the LLBLGen User Manual:

LLBLGen Pro - generated code - filtering and sorting, (Adapter or SelfServicing)

Short Excerpt Follows

FieldCompareSetPredicate Compares the entity field specified with the set of values defined by the subquery elements, using the SetOperator specified. The FieldCompareSetPredicate is the predicate you'd like to use when you want to compare a field's value with a range of values retrieved from another table (or the same table) using a subquery. Due to the big number of constructor overloads, there is no PredicateFactor shortcut, as it would otherwise make the PredicateFactory class to become extremely large in bigger LLBLGen Pro projects (100+ entities).

Code Examples are provided as well.

bclubb wrote:

Using something like this would give you the schema you were looking for. If the relationship already exists between the schema and entries then you should be able to the entity that is returned to access all of the entries that join to it. Let me know if this helps.

 IPredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(CustomerItemPriceSchemaFieldIndex.CustomerItemID, ComparisonOperator.Equal, 10);
ISortExpression sort = new SortExpression(SortClauseFactory.Create(CustomerItemPriceSchemaFieldIndex.ExpirationDate, SortOperator.Descending));
CustomerItemPriceSchemaColletion custs = new CustomerItemPriceSchemaColletion();
custs.GetMulti(filter, 1, sort);
if(custs.Count > 0)
{
    // Do whatever you needed too.
}

but where to use the max function i have to use max of expiration date if u can plz tell me

but guys where to use the max function i have to use max of expiration date,as written by in above sql query if u can plz tell me.

As you use a subquery, you should use a FieldCompareSetPredicate. The setfield should get the AggregateFunction.Max applied to it (field.AggregateFunctionToUse = AggregateFunction.Max). For the Set operator, you use Equal.

You can also do this without 'max' though. Use a FieldCompareSetPredicate, specify an order by on the field where you want the max of, descending, and specify that you want just 1 value (use one of the overloads of FieldCompareSetPredicate's constructors). That will not aggregate and is usually faster.

If you run into problems setting up the predicate with FieldCompareSetPredicate (please check the How Do I? section for an example of the FieldCompareSetpredicate to get an idea how to use it), please paste the code you have so far and we'll help you correct the code.