Convert SQL "HAVING" clause to Dynamic Query

Posts   
 
    
sloscialo
User
Posts: 12
Joined: 09-Dec-2013
# Posted on: 09-Dec-2013 21:32:27   

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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 10-Dec-2013 07:10:26   

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)

David Elizondo | LLBLGen Support Team
sloscialo
User
Posts: 12
Joined: 09-Dec-2013
# Posted on: 10-Dec-2013 23:11:35   

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39888
Joined: 17-Aug-2003
# Posted on: 11-Dec-2013 11:10:50   

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 wink )

(reproduced)

(edit) cause is that the functionmapping replacer visitor doesn't visit the having clause in a group by collection. Fixing....

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39888
Joined: 17-Aug-2003
# Posted on: 11-Dec-2013 12:16:12   

fixed: attached is v4.0 build. v4.1 build will be released friday with installer, as will 4.0

Attachments
Filename File size Added on Approval
SD.LLBLGen.Pro.ORMSupportClasses.zip 412,192 11-Dec-2013 12:16.43 Approved
Frans Bouma | Lead developer LLBLGen Pro
sloscialo
User
Posts: 12
Joined: 09-Dec-2013
# Posted on: 17-Dec-2013 23:17:06   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39888
Joined: 17-Aug-2003
# Posted on: 18-Dec-2013 12:48:25   

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?

Frans Bouma | Lead developer LLBLGen Pro
sloscialo
User
Posts: 12
Joined: 09-Dec-2013
# Posted on: 18-Dec-2013 16:22:35   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39888
Joined: 17-Aug-2003
# Posted on: 19-Dec-2013 10:39:34   

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 simple_smile Glad it's solved simple_smile

Frans Bouma | Lead developer LLBLGen Pro