Hi,

I've got a complex query which I don't seem to get up and running. I'm trying to select Depots who have a positive SumOfQuantity in the TransactionDetail table and are of type 30, but they may NOT have transactions in the period 2006-03-10 / 2006-03-18.

The next SQL statement is the result of the code I've written so far, except for the last condition in the WHERE statement of the subquery : "[dbo].[TRANSACTION].[DepotId] = a.DepotId". How do I refer do the field a.DepotId ?

SQL statement : SELECT DISTINCT a.DepotId, SUM(dbo.TransactionDetail.Quantity) AS SumOfQuantity FROM dbo.Depot INNER JOIN dbo.[Transaction] a ON dbo.Depot.DepotId = a.DepotId INNER JOIN dbo.TransactionDetail ON a.TransactionUId = dbo.TransactionDetail.TransactionUId WHERE (dbo.Depot.DepotTypeId IN (30)) AND (NOT EXISTS (SELECT [dbo].[TRANSACTION].[DepotId] FROM (([dbo].[Depot] INNER JOIN [dbo].[TRANSACTION] ON [dbo].[Depot].[DepotId] = [dbo].[TRANSACTION].[DepotId]) INNER JOIN [dbo].[TransactionDetail] ON [dbo].[TRANSACTION].[TransactionUId] = [dbo].[TransactionDetail].[TransactionUId]) WHERE (([dbo].[TRANSACTION].[TransactionDate] BETWEEN '2006-03-10' AND '2006-03-18')) AND [dbo].[TRANSACTION].[DepotId] = a.DepotId)) GROUP BY a.DepotId

Hope anyone has a solution for this ?!

Greetings, Danny

Please use FieldCompareSetPredicate for the inner(sub) Query.

Please refer to the LLBLGen Pro documentation "Using the generated code -> Adapter/SelfServicing -> Filtering and Sorting -> The predicate system"

Also you would find many examples in the forum, just search for "FieldCompareSetPredicate"

Hi Walaa,

I already did use the FieldCompareSetPredicate as follows (part of the code) :

relations.Add(EntityClasses.TransactionEntity.Relations.DepotEntityUsingDepotId) relations.Add(EntityClasses.TransactionEntity.Relations.TransactionDetailEntityUsingTransactionUid)

fields.DefineField(TransactionFieldIndex.DepotId, 0, "DepotId") fields.DefineField(TransactionDetailFieldIndex.Quantity, 1, "SumOfQuantity", "", AggregateFunction.Sum) groupByClause.Add(fields(0))

existsFilter.Add(PredicateFactory.Between(TransactionFieldIndex.TransactionDate, dateFrom, dateTo)) existsFilter.Add(PredicateFactory.CompareValue(TransactionFieldIndex.DepotId, _ ComparisonOperator.Equal, New Expression(TransactionFields.DepotId)))

filter.Add(New FieldCompareSetPredicate(TransactionFields.DepotId, TransactionFields.DepotId, SetOperator.Exist, existsFilter, Nothing, "", 0, Nothing, True, Nothing))

havingFilter.Add(New FieldCompareValuePredicate(fields(1), operator, 0)) groupByClause.HavingClause = havingFilter

dao.GetMultiAsDataTable(fields, dt, 0, Nothing, filter, relations, False, groupByClause, Nothing, 0, 0)

When I run the code, an error occurs on the dao.GetMultiAsDataTable : Object must implement IConvertible. When I comment out the last existsFilter (the one with the New Expression), then the query just runs fine and produces the SQL as shown in the first post, but the last filter just has to be included to get the right results.

Any ideas ?

Greetings, Danny

Please check the following code sample in C#:

IPredicateExpression filter = new PredicateExpression();            

filter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(DepotFieldIndex.DepotId), null,
EntityFieldFactory.Create(TRANSACTIONFieldIndex.DepotId),
null, SetOperator.In,               
PredicateFactory.Between(TRANSACTIONFieldIndex.TransactionDate, dateStart, dateEnd),
true));

Also you may find a similar sample in the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1607

Walaa,

The PredicateCompareExpression as used in http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3927 did the job !

greetings, Danny

Otherwise, the "must implement IConvertible" error occurs.