We're using version 4.2.14.0701 against both Oracle and SQL Server.
The trouble I'm having is trying to reproduce a query that looks similar to:
SELECT *
FROM Parent P
WHERE P.Id = :id
AND EXISTS
(
SELECT 1 FROM Extra E
WHERE E.Id = P.Id
HAVING
(
SUM (CASE WHEN (E.Name = 'X' AND E.Value BETWEEN :startValue AND :endValue) THEN 1 ELSE 0 END) = 1
AND
SUM (CASE WHEN (E.Name = 'Y' AND E.Value BETWEEN :startValue AND :endValue) THEN 1 ELSE 0 END) = 1
)
)
Is there anyway to do this with a FieldCompareSetPredicate? I can't figure out how to get the HAVING clause into the filter property of the predicate.
I'm really interested in getting the more complex query into the FieldCompareSetPredicate than rewriting the SQL (unless it's just the EXISTS subquery). Changing all the SQL would require a large refactoring of the code we built on top of LLBLGen...in essence, we have a mechanism that requests a predicate for a predefined query's WHERE clause (so I could pass it a FieldCompareSetPredicate easily). If that can be done, great. If not, we'll have to bite the bullet and refactor the existing code base.
FWIW, our schema has miscellaneous data stored in a separate table. It's keyed by parent id and a data name and it also has a various data fields for strings and numbers. This particular use case tries to determine if two particular rows, representing a single point, are within a square.
Thanks,
Sergio
