Hello,

I would like to code the following query:

select  
    t_articles.doc_i_id, 
    count(t_articles.doc_i_id) 
from 
    t_articles, t_interet_document where 
    t_articles.doc_i_id=t_interet_document.doc_i_id
    and int_i_id in (1023,1200)
group by 
    t_articles.doc_i_id,art_dt_publication
having 
    count(t_articles.doc_i_id)>1

Below is my code so far...

            //Create the 2 fields
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(TArticlesFieldIndex.DocIId, 0, "DocIId", "T_ARTICLES");
            fields.DefineField(TArticlesFieldIndex.DocIId, 1, "NumberOfDocIId", "T_ARTICLES", AggregateFunction.Count);

            //Join tables
            RelationCollection relations = new RelationCollection();
            relations.Add(TArticlesEntity.Relations.TInteretDocumentEntityUsingDocIId);

            //Group by 
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);

            //having
            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(fields[0], null, ComparisonOperator.GreaterThan, 1));
            groupByClause.HavingClause = havingFilter;

            //filter
            IPredicateExpression selectFilter = new PredicateExpression();
            int[] values = new int[2] { 1023, 1200 };
            selectFilter.Add(PredicateFactory.CompareRange(TInteretDocumentFieldIndex.IntIId, values));

            //Sort
            SortExpression sorter = new SortExpression(TArticlesFields.ArtDtPublication | SortOperator.Descending);

            TypedListDAO dao = new TypedListDAO();
            DataTable tlist = new DataTable();


            dao.GetMultiAsDataTable(fields, tlist, 0, sorter, selectFilter, relations, true, groupByClause, null, 0, 0);

Any idea, why it fails at runtime?

Below the error: NullReferencedException was not handled ** Note that if I replace the groupByClause by null, then I will see the right query I am needed (but of course, without the group by.) **

Thanks to help ;-) MT

Ok, Found it ;-)

Replace: havingFilter.Add(new FieldCompareValuePredicate(fields[0], null, ComparisonOperator.GreaterThan, 1));

by

havingFilter.Add(new FieldCompareValuePredicate(fields[1], ComparisonOperator.GreaterThan, 1));

Silat.