Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> May I have example code for Having clause
 

Pages: 1
LLBLGen Pro Runtime Framework
May I have example code for Having clause
Page:1/1 

  Print all messages in this thread  
Poster Message
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# 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))

  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 25-Sep-2006 08:41:28.  
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


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# 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
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# 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

Code:

' 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
Code:
havingFilter.Add(New FieldCompareNullPredicate (fields(1)) )


And to get SUM(WHATEVER) <> SUM(SOMETHING ELSE) use a FieldCompareExpressionPredicate
Code:
havingFilter.Add(New FieldCompareExpressionPredicate(fields(1), Nothing, ComparisonOperator.NotEqual, fields(2)))


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# 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.



[quote][/quote]
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# 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:

Code:
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)


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 29-Sep-2006 02:33:39.  
Code:

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    
  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 29-Sep-2006 02:38:11.  
Code:

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


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 29-Sep-2006 02:45:07.  
I change code to


Code:


     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));
    


Code:


         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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 29-Sep-2006 08:39:38.  
Ok, don't use the factory classes, rather use the following:

Code:
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));


Code:

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


  Top
nopchan
User



Location:

Joined on:
30-Apr-2005 02:19:03
Posted:
56 posts
# Posted on: 03-Oct-2006 03:11:19.  
Walaa wrote:
Ok, don't use the factory classes, rather use the following:

Code:
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));


Code:

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


Code:

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!!!!

Code:

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);


  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.