MS-SQL: select SUM(Miktar*BirimFiyati)/SUM(Miktar) as Toplam,Kodu From Fisler where HareketTuru = '2' or HareketTuru = '3' or HareketTuru = '5' group by Kodu

Normally, I put returning value to the dataset.than I take from dataset and put this result to the another table.

llblgen :

ResultsetFields fields = new ResultsetFields(1);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new  PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(FislerFieldIndex.Kodu));
            
IExpression Tutar   = new Expression(FislerFieldIndex.Miktar,ExOp.Mul,EntityFieldFactory.Create(FislerFieldIndex.BirimFiyati));
fields[0].ExpressionToApply = Tutar;
fields[0].AggregateFunctionToApply  = AggregateFunction.Sum;
int i=1;
IExpression SonucMiktar = new Expression(i,ExOp.Mul,EntityFieldFactory.Create(FislerFieldIndex.Miktar));
fields[1].ExpressionToApply = SonucMiktar;
fields[1].AggregateFunctionToApply  = AggregateFunction.Sum;
IExpression GenelSonuc= new Expression(fields[0],ExOp.Div,fields[1]);

i define the datatable for put this value to it, but i gives reference error. which reference can i use for it? Beside That,I write the sentence like that.is it true or not i don't know. I dont use the filter up to now because i want to put returning values to the another entity.how can i define the this entity and how can i defie the values for this entitiy.

thank you...

Bedouin wrote:

MS-SQL: select SUM(Miktar*BirimFiyati)/SUM(Miktar) as Toplam,Kodu From Fisler where HareketTuru = '2' or HareketTuru = '3' or HareketTuru = '5' group by Kodu

Normally, I put returning value to the dataset.than I take from dataset and put this result to the another table.

llblgen :

ResultsetFields fields = new ResultsetFields(1);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new  PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(EntityFieldFactory.Create(FislerFieldIndex.Kodu));
            
IExpression Tutar   = new Expression(FislerFieldIndex.Miktar,ExOp.Mul,EntityFieldFactory.Create(FislerFieldIndex.BirimFiyati));
fields[0].ExpressionToApply = Tutar;
fields[0].AggregateFunctionToApply  = AggregateFunction.Sum;
int i=1;
IExpression SonucMiktar = new Expression(i,ExOp.Mul,EntityFieldFactory.Create(FislerFieldIndex.Miktar));
fields[1].ExpressionToApply = SonucMiktar;
fields[1].AggregateFunctionToApply  = AggregateFunction.Sum;
IExpression GenelSonuc= new Expression(fields[0],ExOp.Div,fields[1]);

i define the datatable for put this value to it, but i gives reference error. which reference can i use for it?

What error exactly do you get and when? That information would be very helpful

Beside That,I write the sentence like that.is it true or not i don't know. I dont use the filter up to now because i want to put returning values to the another entity.how can i define the this entity and how can i defie the values for this entitiy.

You mean if your expression code is correctly doing what you want to do in SQL?

I think your code should be:

ResultsetFields fields = new ResultsetFields(2);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new  PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));

Expression mulEx = new Expression(
    EntityFieldFactory.Create(FislerFieldIndex.Miktar), ExOp.Mul, 
    EntityFieldFactory.Create(FislerFieldIndex.BrimFiyati));
EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op1.ExpressionToUse = mulEx;
op1.AggregateFunctionToUse = AggregateFunction.Sum;

EntityField op2 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op2.AggregateFunctionToUse = AggregateFunction.Sum;

Expression divEx = new Expression(op1, ExOp.Div, op2);
fields.DefineField(FislerFieldIndex.Miktar, 0, "Toplam");
fields.DefineField(FislerFieldIndex.Kodu, 1, "Kodu);

fields[0].ExpressionToUse = divEx;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

The somewhat weird way to set up the expression will be addressed in 1.0.2005.1 with operator overloading so you can design the expressions in a more intuitive way.

 ResultsetFields fields = new ResultsetFields(2);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));

Expression mulEx = new Expression(
    EntityFieldFactory.Create(FislerFieldIndex.Miktar), ExOp.Mul, 
    EntityFieldFactory.Create(FislerFieldIndex.BrimFiyati));
EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op1.ExpressionToUse = mulEx;
op1.AggregateFunctionToUse = AggregateFunction.Sum;

EntityField op2 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op2.AggregateFunctionToUse = AggregateFunction.Sum;

Expression divEx = new Expression(op1, ExOp.Div, op2);
fields.DefineField(FislerFieldIndex.Miktar, 0, "Toplam");
fields.DefineField(FislerFieldIndex.Kodu, 1, "Kodu);

fields[0].ExpressionToUse = divEx;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

thank you very much otis. this code which you are wrote, return values is it? I want to put each grup value to the another entity.How can I insert these values to it? for example : MalzemeEntity Maliyet = new MalzemeEntity(); how can i insert these values to that entity ?

ResultsetFields fields = new ResultsetFields(2);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));

Expression mulEx = new Expression(
EntityFieldFactory.Create(FislerFieldIndex.Miktar), ExOp.Mul, 
EntityFieldFactory.Create(FislerFieldIndex.BrimFiyati));
EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op1.ExpressionToUse = mulEx;
op1.AggregateFunctionToUse = AggregateFunction.Sum;

EntityField op2 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op2.AggregateFunctionToUse = AggregateFunction.Sum;

Expression divEx = new Expression(op1, ExOp.Div, op2);
fields.DefineField(FislerFieldIndex.Miktar, 0, "Toplam");
fields.DefineField(FislerFieldIndex.Kodu, 1, "Kodu);

fields[0].ExpressionToUse = divEx;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

DataTable tlist = new DataTable();
// when I am write these 

D:ugur\Hareketler\GirisFisiBR.cs(285): The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?) which reference can I define for using datatable?

Bedouin wrote:

ResultsetFields fields = new ResultsetFields(2);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));

Expression mulEx = new Expression(
EntityFieldFactory.Create(FislerFieldIndex.Miktar), ExOp.Mul, 
EntityFieldFactory.Create(FislerFieldIndex.BrimFiyati));
EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op1.ExpressionToUse = mulEx;
op1.AggregateFunctionToUse = AggregateFunction.Sum;

EntityField op2 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op2.AggregateFunctionToUse = AggregateFunction.Sum;

Expression divEx = new Expression(op1, ExOp.Div, op2);
fields.DefineField(FislerFieldIndex.Miktar, 0, "Toplam");
fields.DefineField(FislerFieldIndex.Kodu, 1, "Kodu);

fields[0].ExpressionToUse = divEx;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

DataTable tlist = new DataTable();
// when I am write these 

D:ugur\Hareketler\GirisFisiBR.cs(285): The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?) which reference can I define for using datatable?

Add: using System.Data;

at the top of GirisFisiBR.cs

Bedouin wrote:

ResultsetFields fields = new ResultsetFields(2);
MalzemeYonetimiStokFisHareketleriCollection col = new MalzemeYonetimiStokFisHareketleriCollection();
IPredicateExpression A = new PredicateExpression();
A.Add(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"2"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"3"));
A.AddWithOr(PredicateFactory.CompareValue(FislerFieldIndex.HareketTuru,ComparisonOperator.Equal,"5"));

Expression mulEx = new Expression(
    EntityFieldFactory.Create(FislerFieldIndex.Miktar), ExOp.Mul, 
    EntityFieldFactory.Create(FislerFieldIndex.BrimFiyati));
EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op1.ExpressionToUse = mulEx;
op1.AggregateFunctionToUse = AggregateFunction.Sum;

EntityField op2 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);
op2.AggregateFunctionToUse = AggregateFunction.Sum;

Expression divEx = new Expression(op1, ExOp.Div, op2);
fields.DefineField(FislerFieldIndex.Miktar, 0, "Toplam");
fields.DefineField(FislerFieldIndex.Kodu, 1, "Kodu");

fields[0].ExpressionToUse = divEx;

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

thank you very much otis. this code which you are wrote, return values is it? I want to put each grup value to the another entity.How can I insert these values to it? for example : MalzemeEntity Maliyet = new MalzemeEntity(); how can i insert these values to that entity ?

Read the values in a datatable, then for each row in the datatable you just set fields, like etc.

Maliyet.Toplam = (float)datatable.row[index]["Toplam"];
Maliyet.Kodu = (float)datatable.row[index]["Kodu"];
Maliyet.Save();

thank you very much otis. how can i write for each statement. i am sorry for asking a lot of question otis. again, thank you

D:\GirisFisiBR.cs(277): Cannot implicitly convert type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField' to 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' D:GirisFisiBR.cs(27 : 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' does not contain a definition for 'ExpressionToUse' D:GirisFisiBR.cs(279): 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' does not contain a definition for 'AggregateFunctionToUse'

the code gives that error mesaj otis for op1 and op2 ? how can i solve this problem

error mesaj for these:

EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);

op1.ExpressionToUse = mulEx; op1.AggregateFunctionToUse = AggregateFunction.Sum;

Sorry, my fault,

These should have been: ExpressionToApply and AggregateFunctionToApply

not important, EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);

for this one , the messega is D:\GirisFisiBR.cs(279): Cannot implicitly convert type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField' to 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField'

I should have payed more attention, sorry

EntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar); should be EntityField op1 = (EntityField)EntityFieldFactory.Create(FislerFieldIndex.Miktar);

or you can also use: IEntityField op1 = EntityFieldFactory.Create(FislerFieldIndex.Miktar);

not important , no more questions for today thank you very much otis.

hmm I forgatten that point, How can i fill my datatable with returning value from my Querry.

Bedouin wrote:

hmm I forgatten that point, how can I fill the datatable ?

Please see the documentation: How Do I-> How do I use a group by clause in a dynamic list ? for example to get an idea. or the typedlist/view documentation in using the generated code -> selfservicing -> using typedview/list, which has a section on dynamic lists.