- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Convert SQL "HAVING" clause to Dynamic Query
Joined: 09-Dec-2013
I initially asked this question on StackOverflow at http://stackoverflow.com/questions/20132529/convert-sql-with-having-clause-to-llblgen-pro-dynamicquery and was asked to bring it here.
I am having trouble converting what is a somewhat simple query into an LLBLGen Pro dynamic query. I need to convert something like following SQL (but I'll be passing in values for the region, the id to check and the required names):
SELECT Parent.Id
FROM Parent INNER JOIN Child
ON Child.ParentId = Parent.Id
WHERE Parent.Region = '1'
AND Parent.Id IN (1, 2, 3, 4, 5)
GROUP BY
Parent.Id
HAVING SUM(CASE WHEN child.Name IN ('Required1', 'Required2', 'Required3') THEN 1 ELSE 0 END) != 3
But, I'm having a hard time converting the Having clause into the LLBL equivalent.
This is what I have so far:
var q = qf.Create()
.From(qf.ParentView
.InnerJoin(qf.ChildView.As("child"))
.On(ChildViewFields.ParentId.Source("child") == ParentViewFields.Id))
.Where((ParentViewFields.Region == _region)
.And(ParentViewFields.Id.In(_checkIds)))
.Select(() => ParentViewFields.Id.ToValue<string>())
.GroupBy(ParentViewFields.Id)
.Having( ??? )
I did follow the advice on SO, and tried using something like:
Functions.IIF(ChildViewFields.Name.Source("child").In("Required1", "Required2", "Required3"), 1, 0).Sum().NotEqual(3)
However, that resulted in an ORMQueryConstructionException with a message of "Internal error." I've tried more simplified IIF clauses, but it seems that IIF is what's causing the issue.
One other piece of background is that both ParentView and ChildView are views into the same table. Not sure if that's part of the problem, but there's not much I can do to change it at this point.
Any help would be greatly appreciated. I'm open to alternative suggestions for how to accomplish what the SQL does (caveat is that it must run in the database and not be brought down the client for further computation).
Thanks, Sergio
Hi there. Please post the exact code that produces the exception and the exception's message and full stack trace. Also try to catch the generated SQL to see where is the problem. What LLBLGen version and runtime library version are you using? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)
Joined: 09-Dec-2013
We're using LLBLGen Pro v. 4.0, Library Runtime Version is v2.0.50727
The exception message, as I stated before is a simple: "Internal error."
The stack trace from the ORMQueryConstructionException is:
at SD.LLBLGen.Pro.QuerySpec.FunctionMappingExpression.SD.LLBLGen.Pro.ORMSupportClasses.IExpression.get_LeftOperand()
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IExpression expression)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IEntityField2 field)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.GetFieldPersistenceInfo(IFieldInfo fieldInfo)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IPredicateExpression expression)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.InsertPersistenceInfoObjects(IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PreprocessQueryElements(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQueryAsProjection(IDataAccessAdapter adapter, DynamicQuery query, IGeneralDataProjector projector)
at SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery[TElement](IDataAccessAdapter adapter, DynamicQuery`1 query)
at Slb.Studio.DataAccess.Providers.Llbl.DataAccess.ValidateDatasetChildrenQuery(Int64 workspaceId, IEnumerable`1 entityGuids, DatasetType datasetType, String childProperty, IEnumerable`1 childPropertyValues) in <snip>.cs:line 76
at Slb.Studio.DataAccess.DataAccessTests.ValidationDataAccessTestFixture.DatasetVariableDependencyValidation() in <snip>.cs:line 85
The actual code looks like:
var q = qf.Create()
.From(qf.DatasetWorkspace
.InnerJoin(qf.VariableWorkspace.As("C"))
.On(VariableWorkspaceFields.ParentGuid.Source("C") == DatasetWorkspaceFields.Guid))
.Where((DatasetWorkspaceFields.DatasetType == datasetType)
.And(DatasetWorkspaceFields.Guid.In(guids)))
.Select(() => DatasetWorkspaceFields.Guid.ToValue<string>())
.GroupBy(DatasetWorkspaceFields.Guid)
.Having(Functions.IIF(VariableWorkspaceFields.Name.Source("C").In(depends), VariableWorkspaceFields.Name.Source("C").ToValue<string>(), null).CountDistinct().NotEqual(depends.Count()));
Removing the IIF statement and using a simpler Having clause doesn't cause the exception. Using any IIF statement (in the Having clause), no matter how simple, causes the exception.
Looks like a bug, we'll check it out.
(btw, the ToValue in the having clause shouldn't be there. It 's not the cause of the error, but just pointing it out )
(reproduced)
(edit) cause is that the functionmapping replacer visitor doesn't visit the having clause in a group by collection. Fixing....
fixed: attached is v4.0 build. v4.1 build will be released friday with installer, as will 4.0
Filename | File size | Added on | Approval |
---|---|---|---|
SD.LLBLGen.Pro.ORMSupportClasses.zip | 412,192 | 11-Dec-2013 12:16.43 | Approved |
Joined: 09-Dec-2013
Frans,
I've spent some time testing this, and I think there is another issue. Not sure if it's exactly related or not.
I'm trying now to translate something like the following SQL:
SELECT P.GUID
FROM ENTITYW p
LEFT OUTER JOIN PROPERTYW c ON p.GUID = c.ENTITYGUID
GROUP BY P.GUID
HAVING
NOT
(
SUM(CASE WHEN UPPER(c.Name) IN ('X', 'Y', 'HORIZONTAL_COORDINATE_SYSTEM') THEN 1 ELSE 0 END) = 3
OR
SUM(CASE WHEN UPPER(c.Name) IN ('LATITUDE', 'LONGITUDE', 'HORIZONTAL_COORDINATE_SYSTEM') THEN 1 ELSE 0 END) = 3
)
I've created a QueryFactory that is created as follows:
var havingPredicate = new PredicateExpression();
foreach (var group in dependentPropertyGroups)
{
var normalizedGroup = group.Select(s => s.ToUpperInvariant()).ToList();
havingPredicate.AddWithOr(Functions.IIF(PropertyWorkspaceFields.Name.ToUpper().In(normalizedGroup), 1, 0).SumDistinct().Equal(normalizedGroup.Count()));
}
var q = qf.Create()
.From(qf.EntityHeaderWorkspace
.LeftJoin(qf.PropertyWorkspace)
.On(PropertyWorkspaceFields.EntityGuid == EntityHeaderWorkspaceFields.Guid))
.Where(EntityHeaderWorkspaceFields.Guid.In(entityGuids)
.And(EntityHeaderWorkspaceFields.WorkspaceId == workspaceId))
.Select(() => EntityHeaderWorkspaceFields.Guid.ToValue<string>())
.GroupBy(EntityHeaderWorkspaceFields.Guid)
.Having(!havingPredicate);
I added a couple extra constraints to the Where clause, but I don't think that matters.
In any case, what LLBLGen is creating looks like this:
SELECT TechlogRepository.EntityW.Guid
FROM (TechlogRepository.EntityW
LEFT JOIN TechlogRepository.PropertyW
ON TechlogRepository.PropertyW.EntityGuid =
TechlogRepository.EntityW.Guid)
GROUP BY TechlogRepository.EntityW.Guid
HAVING (NOT ( SUM (
DISTINCT CASE
WHEN CASE
WHEN UPPER (
TechlogRepository.PropertyW.Name) IN ( :p11,
:p13,
:p15)
THEN
1
ELSE
0
END = 1
THEN
:p17
ELSE
:p19
END) = :p9
OR SUM (
DISTINCT CASE
WHEN CASE
WHEN UPPER (
TechlogRepository.PropertyW.Name) IN ( :p23,
:p25,
:p27)
THEN
1
ELSE
0
END = 1
THEN
:p29
ELSE
:p31
END) = :p21))
It's adding an extra WHEN clause that is changing the essence of the query. Is there something I'm doing wrong?
EDIT: I noticed that I had an inaccurate SumDistinct(), so I changed to just Sum(). The essence of the query is retained, but there's still an extra CASE WHEN for each clause added to the predicate.
Thanks,
Sergio
There's no extra 'when' clause, it does this: the inner one performs the actual comparisons, and results in either 1 or 0. The outer then checks whether the value is 1 and returns the first, otherwise returns the second value.
the reason is that it has to convert the boolean expression first and then act accordingly on whether that results in true or false. The boolean expression is the inner case-when, the if/else on the result of that expression is the outer case-when.
I don't know the values of the parameters, could you explain why it changes the intend of the predicate you specified, and give the values of the parameters?
Joined: 09-Dec-2013
Frans,
I was mistaken when I wrote it changed the intent of the query. I accidentally had a SumDistinct() when I shouldn't have (it was a hold-over from a previous cut-and-paste).
However, I was trying to model the specific SQL I wrote before I attempted to write the LLBLGen version.
My SQL's HAVING clause looks like:
HAVING
NOT
(
SUM(CASE WHEN UPPER(c.Name) IN ('X', 'Y', 'CRS') THEN 1 ELSE 0 END) = 3
OR
SUM(CASE WHEN UPPER(c.Name) IN ('LAT', 'LON', 'CRS') THEN 1 ELSE 0 END) = 3
)
LLBLGen Pro generates this:
HAVING (NOT
( SUM
(
CASE WHEN CASE WHEN UPPER
( TechlogRepository.PropertyW.Name ) IN ('X', 'Y', 'CRS') THEN 1 ELSE 0 END = 1 THEN 1 ELSE 0 END
) = 3
OR SUM
(
CASE WHEN CASE WHEN UPPER
( TechlogRepository.PropertyW.Name ) IN ('LAT', 'LON', 'CRS') THEN 1 ELSE 0 END = 1 THEN 1 ELSE 0 END
) = 3
)
)
It looks like it's wrapping the IN statement with a CASE WHEN.
That said, without the SumDistinct() in there, it runs fine. Just was a little confused as to why the extra CASE WHEN was inserted.
Sergio
THe extra case when is indeed redundant if you compare it to the one you wrote by hand. It however translates the query fragments and by doing that it has to fulfill a boolean oriented construct (iif) with two bool values so the necessity of the inner case Glad it's solved