Hi

I have the following Expression in SQL

SELECT PERSONID FROM DONORCHARACTERISTIC WHERE (CHARACTERISTICOPTIONID = 613) AND (CHARACTERISTICDEFINITIONID = 508 ) OR (CHARACTERISTICDEFINITIONID = 504) AND (NUMERICVALUE >= 120) AND (NUMERICVALUE <= 195) GROUP BY PERSONID HAVING (COUNT(PERSONID) = 2)

Im trying to convert this to LLBLGen Pro but simply can´t figure out where to start. I have read the "Field expressions and aggregates" in the documentation but havent found a solution.

Anyone can give me some sample code or point me in the right direction? Would be appriciated very much.

There where clasue (the pridicate) is to be genreated dynamic and so is the count number. But that fact shouldnt have anything to do with the general way to create this expression.

Thanks in regards Anders

To produce the predicate for the Having clause, please do the following:

FieldCompareValuePredicate havingPredicate = PredicateFactory.CompareValue(DONORCHARACTERISTICFieldIndex.PersonID, ComparisonOperator.Equal, 2); havingPredicate.FieldCore.AggregateFunctionToApply = AggregateFunction.Count; IPredicateExpression havingClause = new PredicateExpression(havingPredicate);

Then, set add havingClause as the HavingClause of your GroupByCollection object.

Thanks for your reply

But im still not quite there yet! Havent used this area of the LLBL API yet

I see that I can create a groupByCollection and add a having clause. Fine!

But i need a collection:

DonorcharacteristicCollection col = new DonorcharacteristicCollection();

which i somehow need to add both a select predicate AND the groupby predicate (with the having clause)

in a normal query i would get a LIST of Personid´s where the aggreate expressen was count = 2

So i somehow has to get a collection of DonorcharacteristicCollection

like col.getmulti(SelectPredicate, GroupByCollectionPredicate)

But only getScalar takes a groupbyCollection object which would result in one result which is wrong.

So. Can I both use a groupby, having AND a select predicate and get a collection thet represents this result?

Regards Anders

group by clauses are executed in SQL on the select list. So if you're fetching entities, a group by is meaningless directly in the filter.

what you probably want to do is:

SELECT * FROM Foo WHERE EXISTS (SELECT count(bar) FROM Foo group by SomeField having count(bar)=2)

?

(I simplified the query, is probably not correct SQL, but you get the point). The solution to this is a fieldcomparesetpredicate and a groupby clause. In 1.0.2004.2, I added the group by clause to the FieldCompareSetpredicate.