Hi Friends. Here is my question. I have two tables: PayloadQuery (Id, ClientId, ActivationTime, Interval, Repeats) and PayloadResults (Id, PayloadQueryId, Result). To each query may belong either many results or none at all.
And I need to get a list of queries matching such conditions: select distinct pq from PayloadResults res right join res.PayloadQuery pq where ((pq.ActivationTime<=? or pq.ActivationTime is null) and pq.Client=?) and ( (pq.ActivationTimeis null and count(res) = 0) or (pq.Interval is null and pq.Repeats is null and count(res) = 0) or (pq.Interval is not null and pq.Repeats is null and datediff(minute, pq.ActivationTime, current_timestamp())/pq.Interval > count(res)) or (pq.Interval is not null and pq.Repeats is not null and (datediff(minute, pq.ActivationTime, current_timestamp())/pq.Interval > count(res)) and count(res) < pq.Repeats)) The main problem in this draft is that each "count(res)" should relate to a specific query - count of PayloadResults belonging to this PayloadQuery. I tried to add "group by pq having count(res) < pq.Repeats" but got ActiveRecordException saying: "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." Can someone please explain me how to make this query correct and working? -- 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.
