complex query

Posts   
 
    
braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 27-Jan-2010 17:33:12   

SELECT TOP 1
      [Data]
      ,(SELECT(ColonnaQ) FROM Disp as t2 WHERE t2.ID=MAX(t1.ID))

FROM [Test].[dbo].Disp as t1
  WHERE Data> '2009-11-02'
  GROUP BY Data
  having (SELECT(ColonnaQ) FROM Disp as t2 WHERE t2.ID=MAX(t1.ID)) >0


i tryed to generate this query using a DynamicList, but i have difficult with the alias "t1" and "t2" and with the aggregate filter t2.ID=MAX(t1.ID).

I used a ScalarQueryExpression for (SELECT(ColonnaQ) FROM Disp as t2 WHERE t2.ID=MAX(t1.ID)), but i have no idea on how to reference the external table t1 on the ScalarQueryExpression.

Can you help me?confused

LLBLGen 2.6 Adapter SQL Server 2008

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 27-Jan-2010 18:31:48   

Please post the code you have been trying.

braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 27-Jan-2010 18:43:45   

I tried a lot of code, the last was:


 ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(Disp.Data, 0);
        fields.DefineField(new EntityField2("QtaDisp",new ScalarQueryExpression(
            DispFields.ColonnaQ.SetObjectAlias("t2"),(DispFields.ID == DispFields.ID.SetAggregateFunction(AggregateFunction.Max))
            )
            ),1);

 PredicateExpression exp = new PredicateExpression();
        exp.Add(DispFields.Data > dataQuery );
        
        GroupByCollection grpBy = new GroupByCollection(DispFields.Data);
grpBy.HavingClause = new PredicateExpression(fields["QtaDisp"].SetAggregateFunction(AggregateFunction.Max) > 0);

DataTable res = new DataTable();

        using (DataAccessAdapter da = new DataAccessAdapter())
        {
            da.FetchTypedList(fields, res, new RelationPredicateBucket(exp), 0, null, true, grpBy);
        }


Thanks Davide

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 28-Jan-2010 06:01:01   

As you are using Aliases for entities, you should set the alias in every field you use. Here is a re-formatted version of your code

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(Disp.Data, 0, "Data", "t1");
fields.DefineField(new EntityField2("QtaDisp",new ScalarQueryExpression(
     DispFields.ColonnaQ.SetObjectAlias("t2"),(DispFields.ID.SetObjectAlias("t2") == 
     DispFields.ID.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max)) ) ),1);

PredicateExpression exp = new PredicateExpression();
exp.Add(DispFields.Data > dataQuery );

// as this field is the same you defined earlier, just reference it...
GroupByCollection grpBy = new GroupByCollection(fields[0]);
grpBy.Add(fields[1]);

// as this field is the same you defined earlier, just reference it...
grpBy.HavingClause = new PredicateExpression(fields[1] > 0);

// the rest...
David Elizondo | LLBLGen Support Team
braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 28-Jan-2010 10:13:31   

Hi daelmo,

thanks, I added the objects alias and it seems works on generated query, but there is a problem with the scalarquery filter's, bacause it doesn't generate the MAX() aggregate function.

On the expFieldQta PredicateExpression I have the MAX function on t1.DispId, but the DQE generates: WHERE ( [t2].[ID] = [t1].[ID]). It should be WHERE ( [t2].[ID] = MAX([t1].[ID])), bacause in the code I have DispFields.DispId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max)

It also produce this Exception, but i think that is related to the wrong filter, because the GroupBy Having clause refers to the same EntityField that contains the wrong filter (fields["QtaDisp"]).


An exception was caught during the execution of a retrieval query: Column 'db.dbo.Disp.ID' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.. Check InnerException, 

This is the complete code, and the query generated.



DateTime dataQuery = new DateTime(2009,12,01).Date;

        ResultsetFields fields = new ResultsetFields(2);
        fields.DefineField(DispFields.DispData.SetObjectAlias("t1"),0);     
        
        PredicateExpression expFieldQta = new PredicateExpression();        
        expFieldQta.Add(DispFields.DispId.SetObjectAlias("t2") == DispFields.DispId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max));
        fields.DefineField(new EntityField2("QtaDisp",new ScalarQueryExpression(
            DispFields.DispXlsColQ.SetObjectAlias("t2"),expFieldQta)),1);
    
        
        PredicateExpression exp = new PredicateExpression();
        exp.Add(
            DispFields.DispData.SetObjectAlias("t1") > dataQuery &
            DispFields.DispAnagId.SetObjectAlias("t1") == anagId &
            new EntityField2("DifferenzaData", new DbFunctionCall("DATEDIFF(d,{0},{1})", new object[] { dataQuery, DispFields.DispData.SetObjectAlias("t1") })) <= 45           
            );
        
        GroupByCollection grpBy = new GroupByCollection(fields[0]);
        grpBy.HavingClause = new PredicateExpression(new FieldCompareValuePredicate(fields["QtaDisp"] ,null,ComparisonOperator.GreaterThan, 0));




Query: SELECT [t1].[Data] AS [Data], 
(SELECT [t2].[ColonnaQ] AS [ColonnaQ] FROM [db].[dbo].[Disp] [t2]  WHERE ( [t2].[ID] = [t1].[ID])) AS [QtaDisp] FROM [db].[dbo].[Disp] [t1]  WHERE ( ( ( ( [t1].[Data] > @TmpdispData1 AND [t1].[AnagId] = @TmpdisAnagId2) AND DATEDIFF(d,@LO54da80304,[t1].[Data]) <= @DifferenzaData3))) GROUP BY [t1].[Data] HAVING ( (SELECT [t2].[ColonnaQ] AS [ColonnaQ] FROM [db].[dbo].[Disp] [t2]  WHERE ( [t2].[ID] = [t1].[ID])) > @QtaDisp5)
    Parameter: @TmpdispData1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 01/12/2009 0.00.00.
    Parameter: @TmpdisAnagId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1990.
    Parameter: @LO54da80304 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 01/12/2009 0.00.00.
    Parameter: @DifferenzaData3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 45.
    Parameter: @QtaDisp5 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.


Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 28-Jan-2010 11:44:54   

Reproduced.

Looking into it.

braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 28-Jan-2010 11:47:12   

thank you!

braidiano
User
Posts: 40
Joined: 18-Nov-2006
# Posted on: 28-Jan-2010 22:54:59   

Hi,

have you news? simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jan-2010 10:47:02   

In 2004 we introduced aggregate functions. During development we quickly ran into a problem. Say you do this:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Max), 0);

PredicateExpression filter = new PredicateExpression(new FieldCompareValueExpression(fields[0], ComparisonOperator.Equal, 3));

This is a problem, because the aggregate is for the projection (resultsetfields) but will also be applied in the predicate. To avoid that, we only allowed aggregates in predicates in having clauses. This is the reason why you don't see it appear in the scalar. In practice this worked because there's almost no use case for aggregates in predicates, except perhaps this one.

I can reproduce this behavior you're seeing with:


[Test]
public void MaxInScalarAndHavingTest()
{
    ResultsetFields fields = new ResultsetFields(2);
    fields.DefineField(OrderFields.CustomerId.SetObjectAlias("t1"), 0);

    PredicateExpression expFieldQta = new PredicateExpression();
    expFieldQta.Add(OrderFields.OrderId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max) == OrderFields.OrderId.SetObjectAlias("t2"));

    IExpression scalarQuery = new ScalarQueryExpression(OrderFields.Freight.SetObjectAlias("t2"), expFieldQta);
    fields.DefineField(new EntityField2("QtaDisp", scalarQuery), 1);
    RelationPredicateBucket filter = new RelationPredicateBucket(OrderFields.ShippedDate.SetObjectAlias("t1") > DateTime.Parse("1/1/1990"));

    GroupByCollection grpBy = new GroupByCollection(fields[0]);
    grpBy.HavingClause = new PredicateExpression(new EntityField2("HC", scalarQuery) > 0);
    DataTable dt = new DataTable();
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchTypedList(fields, dt, filter, 0, null, false, grpBy);
    }
}

which crashes as the MAX aggregate isn't shown in the predicate. It's not shown there because it's part of a scalar query, and it doesn't know it's part of the having clause (the first one will never have the MAX).

So that's the background on this, but how to fix it for your situation? We can't change the library behavior as that would break applications, what we can do is offer you a workaround for this situation so you can construct the query you want. If you look at my query above, you can make the following change: use a DbFunctionCall, to introduce the 'Max' instead of the aggregatefunction: So instead of: expFieldQta.Add(OrderFields.OrderId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max) == OrderFields.OrderId.SetObjectAlias("t2"));

I do:

expFieldQta.Add(new EntityField2("OID", new DbFunctionCall("MAX({0})", new object[] { OrderFields.OrderId.SetObjectAlias("t1") }))
                                    == OrderFields.OrderId.SetObjectAlias("t2"));

which simply wraps the field in MAX(). It's a workaround which is necessary only in situations where you use an aggregate in a predicate which is usually not that common.

We do realize this isn't great, and apologize for this workaround, however we can't change it in the framework as that would break the code which utilizes this feature (as I explained in the beginning of my post).

Frans Bouma | Lead developer LLBLGen Pro