May I have example code for Having clause

Posts   
 
    
nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 25-Sep-2006 07:31:49   

May I have example code for Having clause :

SELECT dbo.gljour.gljour_no as voucherno, SUM(dbo.gljourls.gljourls_dr) AS debit, SUM(dbo.gljourls.gljourls_cr) AS credit FROM dbo.gljour FULL OUTER JOIN dbo.gljourls ON dbo.gljour.gljour_no = dbo.gljourls.gljour_no AND dbo.gljour.branch_id = dbo.gljourls.branch_id GROUP BY dbo.gljour.gljour_no HAVING (SUM(dbo.gljourls.gljourls_dr) IS NULL) OR (SUM(dbo.gljourls.gljourls_dr) <> SUM(dbo.gljourls.gljourls_cr))

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 26-Sep-2006 00:17:34   

Walaa wrote:

There are some examples on the forums, please check the following related threads: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4792 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6703 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6490 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5086 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5668

I can write code for sum(dbo.gljourls.gljourls_dr) > 0 but I can not write code for (SUM(dbo.gljourls.gljourls_dr) <> SUM(dbo.gljourls.gljourls_cr)) and I can not write code for SUM(dbo.gljourls.gljourls_dr) IS NULL

please, help me for example code

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Sep-2006 08:21:27   

Taking the example given in the first thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4792


' HAVING Sum(UnitPrice * Quantity * (1 - Discount)) > 1000

Dim havingFilter As IPredicateExpression = New PredicateExpression
havingFilter.Add(New FieldCompareValuePredicate(fields(1), Nothing, ComparisonOperator.GreaterThan, 1000.0F))
groupByClause.HavingClause = havingFilter

In the same manner: To get Sum(WHATEVER) IS NULL use a FieldCompareNullPredicate instead

havingFilter.Add(New FieldCompareNullPredicate (fields(1)) )

And to get SUM(WHATEVER) <> SUM(SOMETHING ELSE) use a FieldCompareExpressionPredicate

havingFilter.Add(New FieldCompareExpressionPredicate(fields(1), Nothing, ComparisonOperator.NotEqual, fields(2)))

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 28-Sep-2006 11:56:59   

ResultsetFields fieldsn = new ResultsetFields(6); fieldsn.DefineField(GljourFieldIndex.GljourNo, 0, "GljourNo", "Gljour"); fieldsn.DefineField(GljourFieldIndex.BranchId, 1, "BranchId", "Gljour");

fieldsn.DefineField(GljourFieldIndex.GljourDate, 2, "GljourDate", "Gljour", AggregateFunction.Max); fieldsn.DefineField(GljourFieldIndex.GljourDesc, 3, "GlJourDesc", "Gljour", AggregateFunction.Max);

fieldsn.DefineField(GljourlsFieldIndex.GljourlsDr, 4, "GljourlsDr", "Gljourls", AggregateFunction.Sum); fieldsn.DefineField(GljourlsFieldIndex.GljourlsCr, 5, "GljourlsCr", "Gljourls", AggregateFunction.Sum);

IPredicateExpression B = new PredicateExpression(); B.Add(PredicateFactory.CompareValue(GljourFieldIndex.BranchId, ComparisonOperator.Equal, ConfigBranch)); B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.GreaterEqual, SelectFrom.EditValue)); B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.LessEqual, SelectTo.EditValue));

IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(GljourEntity.Relations.GljourlsEntityUsingGljourNoBranchId, "Gljourls", "Gljour", JoinHint.Right);

bucket.PredicateExpression.Add(B); ISortExpression sorterGljour = new SortExpression( SortClauseFactory.Create(GljourFieldIndex.GljourNo, SortOperator.Descending));

IPredicateExpression havingFilter = new PredicateExpression(); havingFilter.Add(new FieldCompareNullPredicate(GljourlsFields.GljourlsDr, null)); havingFilter.AddWithOr(new FieldCompareExpressionPredicate(fieldsn[4], null, ComparisonOperator.NotEqual, new Expression(fieldsn[5])));

IGroupByCollection groupByClause = new GroupByCollection(); groupByClause.Add(fields[0]); groupByClause.Add(fields[1]); groupByClause.HavingClause = havingFilter;

DataTable dynamicList = new DataTable();

    adapter.FetchTypedList(fieldsn, dynamicList, bucket, 0, sorterGljour , true, groupByClause);

It's not work I have error

The multi-part identifier "essdata.dbo.gljour.branch_id" could not be bound. The multi-part identifier "essdata.dbo.gljour.gljour_date" could not be bound.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Sep-2006 16:42:00   

In your predicates, sortClause and GroupByClause use the dynamicList fields instead of the EntityFieldIndex Fields, as follows:

B.Add(PredicateFactory.CompareValue(fieldsn[1], ComparisonOperator.Equal, ConfigBranch));

Also note that you have used "fields[0]" in the groupByClause, while there is no definition for fields, only "fieldsn". (This code should not compile)

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 29-Sep-2006 02:33:39   

B.Add(PredicateFactory.CompareValue(fieldsn[1], ComparisonOperator.Equal, ConfigBranch));

This code should not compile

Error 3 The best overloaded method match for 'DataModel.FactoryClasses.PredicateFactory.CompareValue(DataModel.AccFieldIndex, SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator, object)' has some invalid arguments

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 29-Sep-2006 02:38:11   

   ISortExpression sorterGljour = new SortExpression(
                SortClauseFactory.Create(fieldsn[1], SortOperator.Descending));
    

Error 5 The best overloaded method match for 'DataModel.FactoryClasses.SortClauseFactory.Create(DataModel.AccFieldIndex, SD.LLBLGen.Pro.ORMSupportClasses.SortOperator)' has some invalid arguments C:\Ess2006V2\Account\JournalCheckBalance.cs 188 17 Account

Error 6 Argument '1': cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField2' to 'DataModel.AccFieldIndex' C:\Ess2006V2\Account\JournalCheckBalance.cs 188 42 Account

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 29-Sep-2006 02:45:07   

I change code to



       B.Add(PredicateFactory.CompareValue(GljourFieldIndex.BranchId, ComparisonOperator.Equal, ConfigBranch));
           B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.GreaterEqual, SelectFrom.EditValue));
           B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.LessEqual, SelectTo.EditValue));
    



         havingFilter.Add(new FieldCompareNullPredicate(fieldsn[4], null));
            havingFilter.AddWithOr(new FieldCompareExpressionPredicate(fieldsn[4], null, ComparisonOperator.NotEqual, new Expression(fieldsn[5])));

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fieldsn[0]);
            groupByClause.Add(fieldsn[1]);
            groupByClause.HavingClause = havingFilter;

          DataTable dynamicList = new DataTable();
            adapter.FetchTypedList(fieldsn, gljourSumTypedList1, bucket, 0, sorterGljour, true, groupByClause);


It's not work I have error

The multi-part identifier "essdata.dbo.gljour.branch_id" could not be bound. The multi-part identifier "essdata.dbo.gljour.gljour_date" could not be bound.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Sep-2006 08:39:38   

Ok, don't use the factory classes, rather use the following:

B.Add(New FieldCompareValuePredicate(GljourFieldIndex.BranchId, ComparisonOperator.Equal, ConfigBranch));
         B.AddWithAnd(New FieldCompareValuePredicate(GljourFieldIndex.GljourDate, ComparisonOperator.GreaterEqual, SelectFrom.EditValue));
         B.AddWithAnd(New FieldCompareValuePredicate(GljourFieldIndex.GljourDate, ComparisonOperator.LessEqual, SelectTo.EditValue));


SortExpression sorterGljour = new SortExpression();
sorterGljour.Add(New SortClause(fieldsn[1], SortOperator.Descending));

nopchan
User
Posts: 56
Joined: 30-Apr-2005
# Posted on: 03-Oct-2006 03:11:19   

Walaa wrote:

Ok, don't use the factory classes, rather use the following:

B.Add(New FieldCompareValuePredicate(GljourFieldIndex.BranchId, ComparisonOperator.Equal, ConfigBranch));
         B.AddWithAnd(New FieldCompareValuePredicate(GljourFieldIndex.GljourDate, ComparisonOperator.GreaterEqual, SelectFrom.EditValue));
         B.AddWithAnd(New FieldCompareValuePredicate(GljourFieldIndex.GljourDate, ComparisonOperator.LessEqual, SelectTo.EditValue));


SortExpression sorterGljour = new SortExpression();
sorterGljour.Add(New SortClause(fieldsn[1], SortOperator.Descending));


sorterGljour.Add(New SortClause(fieldsn[1], SortOperator.Descending));

Argument '1' : cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField2' to 'DataModel.AccFieldIndex' The best overloaded method match for 'DataModel.FactoryClasses.SortClauseFactory.Create (DataModel.AccFieldIndex,SD.LLBLGen.Pro.ORMSupportClasses.SortOperator)' has some invalid arguments

I change code to :

code] sorterGljour.Add(New SortClause(GljourFieldIndex.GljoourNo,SortOperator.Descending));

I can compile it.

but I have SQlEXCEPTION ERROR

The multi-part identifier "databasename.dbo.tablename.fieldname" could not be bound.

I change code to use Typedlist Designer with code then work OK!!!!


gljourSumTypedList1 = new DataModel.TypedListClasses.GljourSumTypedList();

IPredicateExpression B = new PredicateExpression();
IEntityFields2 fields = gljourSumTypedList1.GetFieldsInfo();

ResultsetFields fieldsn = new ResultsetFields ;
fieldsn.DefineField(fields[0], 0, "GljourNo", "Gljour");
fieldsn.DefineField(fields[1], 1, "BranchId", "Gljour");
fieldsn.DefineField(fields[2], 2, "GljourDate", "Gljour", AggregateFunction.Max);
fieldsn.DefineField(fields[3], 3, "GlJourDesc", "Gljour", AggregateFunction.Max);
fieldsn.DefineField(fields[4], 4, "GljourlsDr", "Gljourls", AggregateFunction.Sum);
fieldsn.DefineField(fields[5], 5, "GljourlsCr", "Gljourls", AggregateFunction.Sum);

B.Add(PredicateFactory.CompareValue(GljourFieldIndex.BranchId, ComparisonOperator.Equal, ConfigBranch));
B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.GreaterEqual, SelectFrom.EditValue));
B.AddWithAnd(PredicateFactory.CompareValue(GljourFieldIndex.GljourDate, ComparisonOperator.LessEqual, SelectTo.EditValue));
    
IRelationPredicateBucket bucket = (IRelationPredicateBucket)gljourSumTypedList1.GetRelationInfo();

bucket.PredicateExpression.Add[B]; 
ISortExpression sorterGljour = new SortExpression(
          SortClauseFactory.Create(GljourFieldIndex.GljourNo, SortOperator.Descending));
IPredicateExpression havingFilter = new PredicateExpression();

havingFilter.Add(new FieldCompareNullPredicate(fields[4], null));
havingFilter.AddWithOr(new FieldCompareExpressionPredicate(fields[4], null, ComparisonOperator.NotEqual, new Expression(fields[5])));

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
groupByClause.HavingClause = havingFilter; 

adapter.FetchTypedList(gljourSumTypedList1.GetFieldsInfo(), gljourSumTypedList1, bucket, 0, sorterGljour, true, groupByClause);