Sorry guys, I'm back again....

This code

        ErrorCheckQueryItem CreateCommissionStatementAmountErrorCheckItem()
        {
            var qf = new QueryFactory();

            var grossTransactionTotalExpression = new Expression(Functions.Coalesce(CommissionStatementFields.Amount, 0), ExOp.Add, Functions.Coalesce(CommissionStatementFields.VATAmount, 0));
            var grossItemTotalExpression = new Expression(Functions.Coalesce(CommissionItemFields.Amount, 0).Sum(), ExOp.Add, Functions.Coalesce(CommissionItemFields.VATAmount, 0).Sum());
            var inner = qf.Create("x")
                .From(qf.CommissionStatement.LeftJoin(CommissionStatementEntity.Relations.CommissionItemEntityUsingCommissionStatementID))
                .Select(() => new
                              {
                                  ID = CommissionStatementFields.ID.ToValue<int>(),
                                  GrossTransactionTotal = grossTransactionTotalExpression.As("GrossTransactionTotal").ToValue<decimal>(),
                                  GrossItemTotal = grossItemTotalExpression.As("GrossItemTotal").ToValue<decimal>(),
                              })
                .GroupBy(CommissionStatementFields.ID, grossTransactionTotalExpression);

            var query = qf.Create()
                .SelectFrom(inner)
                .Where(inner.Field("GrossTransactionTotal").NotEqual(inner.Field("GrossItemTotal")));

(see I'm trying to use subqueries just like Frans commanded me tosuggested I should )

This is the generated SQL which fails:

DECLARE @p2 int; SET @p2='0'
DECLARE @p4 int; SET @p4='0'
DECLARE @p6 int; SET @p6='0'
DECLARE @p8 int; SET @p8='0'
DECLARE @p10 int; SET @p10='0'
DECLARE @p12 int; SET @p12='0'

SELECT
  [LPA_x1].[ID],
  [LPA_x1].[GrossTransactionTotal],
  [LPA_x1].[GrossItemTotal] 
FROM
  (
SELECT
  [CommissionStatement].[ID],
  (COALESCE([AccountsTransaction].[Amount],
  @p2) + COALESCE([AccountsTransaction].[VATAmount],
  @p4)) AS [GrossTransactionTotal],
  (SUM(COALESCE([AnalysisItem].[Amount],
  @p6)) + SUM(COALESCE([AnalysisItem].[VATAmount],
  @p8))) AS [GrossItemTotal] 
FROM
  (((( [AccountsTransaction]  
INNER JOIN
  [CommissionStatement] ON [AccountsTransaction].[ID] = [CommissionStatement].[ID]) 
LEFT JOIN
  [CommissionItem] ON [CommissionStatement].[ID] = [CommissionItem].[CommissionStatementID]) 
LEFT JOIN
  [SaleAnalysisItem] ON [SaleAnalysisItem].[ID] = [CommissionItem].[ID]) 
LEFT JOIN
  [AnalysisItem] ON [AnalysisItem].[ID] = [SaleAnalysisItem].[ID]) 
GROUP BY
  [CommissionStatement].[ID],
  (COALESCE([AccountsTransaction].[Amount],
  @p10) + COALESCE([AccountsTransaction].[VATAmount],
  @p12))) [LPA_x1] 
WHERE
  ( ( [LPA_x1].[GrossTransactionTotal] <> [LPA_x1].[GrossItemTotal]))

Now this fails only because the parameter numbers in the GroupBy are different from those in the SELECT. SQL thus assumes they are different and the query fails. If I change the ones in the GROUPBY to @p2 and @p4 then it works.

1) Why are the parameter numbers counting in twos? 2) Why are parameters used at all? Or at least why for a simple COALESCE(x, 0)? 3) Can the same parameter not be reused for identical values? 4) How can I get this query to work?

Cheers Simon

Hi Simon,

Here is the explanation for this and the possible workarounds: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=21115&StartAtMessage=0&#119085

This code did the trick for me:-

        public static IEntityFieldCore CoalesceToZero(this EntityField2 field)
        {
            return BlockFactory.CreateExpressionField(new DbFunctionCall("COALESCE({0}, '0')", new object[] { field }));
        }

No Declares; no duplicated constants; Group By just works; no third level of nesting.