NHibernate 2.1.2 and SQLServer 2005
I am trying to implement a Criteria query that generates a HAVING
clause with a NotEqual condition. Here's my query with a Gt
restriction
ICriteria query = session.CreateCriteria<Activity>()
.Add(Subqueries.PropertyIn("ActivityId",
DetachedCriteria.For<Milestone>()
.SetProjection(Projections.GroupProperty("Activity"))
.Add(Restrictions.Gt(Projections.Sum("MilestoneAmount"),
0))));
and this is the generated T-SQL, which runs correctly
SELECT this_.ACTIVITY as ACTIVITY5_0_, this_.CONTRACT as CONTRACT5_0_
FROM prod.lawson.ACACTIVITY this_ WHERE this_.ACTIVITY in (SELECT
this_0_.ACTIVITY as y0_ FROM prod.lawson.ACMILESTNX this_0_ GROUP BY
this_0_.ACTIVITY HAVING sum(this_0_.BILL_AMOUNT) > @p0);@p0 = 0
When I change the Gt to NotEqual as follows (note the ! added to the
restriction on the Sum projection)
ICriteria query = session.CreateCriteria<Activity>()
.Add(Subqueries.PropertyIn("ActivityId",
DetachedCriteria.For<Milestone>()
.SetProjection(Projections.GroupProperty("Activity"))
.Add(!
Restrictions.Eq(Projections.Sum("MilestoneAmount"), 0))));
it generates the following T-SQL
SELECT this_.ACTIVITY as ACTIVITY5_0_, this_.CONTRACT as CONTRACT5_0_
FROM prod.lawson.ACACTIVITY this_ WHERE this_.ACTIVITY in (SELECT
this_0_.ACTIVITY as y0_ FROM prod.lawson.ACMILESTNX this_0_ WHERE not
(sum(this_0_.BILL_AMOUNT) = @p0) GROUP BY this_0_.ACTIVITY);@p0 = 0
which results in the following error
An aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list, and the column
being aggregated is an outer reference.
How can I change my query so that the generated T-SQL is
SELECT this_.ACTIVITY as ACTIVITY5_0_, this_.CONTRACT as CONTRACT5_0_
FROM prod.lawson.ACACTIVITY this_ WHERE this_.ACTIVITY in (SELECT
this_0_.ACTIVITY as y0_ FROM prod.lawson.ACMILESTNX this_0_ GROUP BY
this_0_.ACTIVITY HAVING sum(this_0_.BILL_AMOUNT) <> @p0);@p0 = 0
That is, the original query with the > in the HAVING clause replaced
by <>. Presumably, I could just use Restrictions.Ne, if it were
available.
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
