Hi,

I need to perform the next query using llblgen:

 SELECT SUM((PrecioLocal*Unidades)-(PrecioLocal*Unidades*Dto/100)) / SUM(Unidades) FROM VentasLocal  WHERE ( Articulo = @Artic)

I’m using the next code to do it:

    // PrecioLocal * Unidades
IExpression eP1 = new Expression(EntityFieldFactory.Create(VentasLocalFieldIndex.PrecioLocal), ExOp.Mul, EntityFieldFactory.Create(VentasLocalFieldIndex.Unidades));
// (PrecioLocal * Unidades) * Dto
IExpression eP2 = new Expression(eP1, ExOp.Mul, EntityFieldFactory.Create(VentasLocalFieldIndex.Dto));
// ((PrecioLocal * Unidades) * Dto) / 100
IExpression eP3 = new Expression(eP2, ExOp.Div, 100);
// (PrecioLocal * Unidades)-((PrecioLocal * Unidades) * Dto) / 100
IExpression eP4 = new Expression(eP1, ExOp.Sub, eP3);

// sum((PrecioLocal * Unidades)-((PrecioLocal * Unidades) * Dto) / 100)
IEntityField2 op1=EntityFieldFactory.Create(VentasLocalFieldIndex.PrecioLocal);
op1.ExpressionToApply=eP4;
op1.AggregateFunctionToApply=AggregateFunction.Sum;

// sum(unidades)
IEntityField2 op2=EntityFieldFactory.Create(VentasLocalFieldIndex.Unidades);
op2.AggregateFunctionToApply=AggregateFunction.Sum;

// sum((PrecioLocal * Unidades)-((PrecioLocal * Unidades) * Dto) / 100) / sum(unidades)
IExpression expRes=new Expression(op1, ExOp.Div, op2);

IPredicate filter = PredicateFactory.CompareValue(VentasLocalFieldIndex.Articulo, ComparisonOperator.Equal, articulo);
object r=Adapter.GetScalar(EntityFieldFactory.Create(VentasLocalFieldIndex.PrecioLocal), expRes, AggregateFunction.None, filter);

Unfortunately does not work. I checked the SQL using SQLProfiler and the generated code is nearly identical as my target query but one parameter is missing:

exec sp_executesql N'SELECT TOP 1 SUM(([NovaEngel05].[dbo].[VentasLocal].[PrecioLocal] * [NovaEngel05].[dbo].[VentasLocal].[Unidades]) - ((([NovaEngel05].[dbo].[VentasLocal].[PrecioLocal] * [NovaEngel05].[dbo].[VentasLocal].[Unidades]) * [NovaEngel05].[dbo].[VentasLocal].[Dto]) / @LO17331)) / SUM([NovaEngel05].[dbo].[VentasLocal].[Unidades]) AS [PrecioLocal] FROM [NovaEngel05].[dbo].[VentasLocal]  WHERE ( [NovaEngel05].[dbo].[VentasLocal].[Articulo] = @Articulo2)', N'@Articulo2 int', @Articulo2 = 11711

The @LO17331 parameter is not defined (eq 100).

Do you have any idea what’s wrong with my code?

Thanks

You get an error I pressume?

The LO<hashcode> parameter is created for the value, indeed, it should have been added to the query normally. It is initialized with a value, but apparently not appended to the SqlCommand's parameter collection. I'll check it out.

(edit) you add P1 to 2 different expression objects: P2 and P4. This could be the cause. I'll check further... it works when I specify a value in an expression, so it's something with complex nested expressions apparently.

Yes, I'm getting an SQL error: I have to declare the LO.... param

I don't really need a fast fix; I will be on holidays the next week so don’t worry if you are busy now, I can wait a couple of weeks

I'll see if I can reproduce it with one expression being added to 2 other expressions.

FIxed in next build.

Fix is now available (Runtime libraries)

Otis wrote:

Fix is now available (Runtime libraries)

Thanks a lot.