Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> How to implement group by and having
 

Pages: 1
LLBLGen Pro Runtime Framework
How to implement group by and having
Page:1/1 

  Print all messages in this thread  
Poster Message
like2175
User



Location:
Camberley, United Kingdom
Joined on:
27-Mar-2006 16:16:06
Posted:
83 posts
# Posted on: 28-Apr-2009 13:04:31.  
I realise that the answers are probably in the documentation but I've be staring at it for hours and am still really struggling to implement this relatively simple sql via llblgenpro. If someone could explain the steps to implement this I would be very grateful:

Code:
SELECT TripStopAllActivityFullV.Name
FROM TripStopAllActivityFullV
WHERE (((TripStopAllActivityFullV.Arrive)<Now()))
GROUP BY TripStopAllActivityFullV.Name
HAVING (((Sum(TripStopAllActivityFullV.Activity_Type_Id))=1));


I have a TripStopAllActivityFullVCollection containing TripStopAllActivityFullVEntities. Do I need a TypedList/TypedView?

2 class, self serve, vb 2008, llblgen v2.6, access db.
  Top
MTrinder
User



Location:
London by day, Milton Keynes by night.
Joined on:
08-Oct-2008 17:55:47
Posted:
1461 posts
# Posted on: 28-Apr-2009 21:11:59.  
Have you read the documentation about using Having and Group By in self servicing here ?

If you have, please can you post the code as you have got it so far, and well have a stab at getting it to where you need to.

Matt


  Top
like2175
User



Location:
Camberley, United Kingdom
Joined on:
27-Mar-2006 16:16:06
Posted:
83 posts
# Posted on: 29-Apr-2009 08:20:38.  
Thanks MTrinder. I've got is sorted now!

Code:
Dim fields As New ResultsetFields(2)
        fields.DefineField(TripStopPersonActivityFields.PersonId, 0)
        fields.DefineField(TripStopPersonActivityFields.PersonActivityTypeId, 1, "OnOffBoard", AggregateFunction.Sum)
        Dim relations As IRelationCollection = New RelationCollection()
        relations.Add(TripStopPersonActivityEntity.Relations.TripStopEntityUsingTripStopId)

        Dim groupByClause As IGroupByCollection = New GroupByCollection()
        groupByClause.Add(fields(0))

        groupByClause.HavingClause = New PredicateExpression( _
         fields(1).SetExpression(TripStopPersonActivityFields.PersonActivityTypeId * 1).SetAggregateFunction(AggregateFunction.Sum) = 1)

        Dim filter As IPredicateExpression = ((TripStopFields.LocationId = _tripStop.LocationId) And _
             (TripStopFields.StatusId <> StatusEntity.Deleted) And _
             (TripStopFields.Etd <= _tripStop.Etd))

        Dim dynamicList As New DataTable()
        Dim dao As New TypedListDAO()
        dao.GetMultiAsDataTable(fields, dynamicList, 0, Nothing, filter, relations, True, groupByClause, Nothing, 0, 0)

        lbPersonId.DataSource = dynamicList
        lbPersonId.DisplayMember = "PersonId"
        lbPersonId.ValueMember = "PersonId"



Which produces:

Code:
Query: SELECT [Trip_Stop_Person_Activity].[Person_ID] AS [PersonId], SUM([Trip_Stop_Person_Activity].[Person_Activity_type_ID] * @LLBLEP1) AS [OnOffBoard] FROM ( [Trip_Stop] INNER JOIN [Trip_Stop_Person_Activity] ON [Trip_Stop].[Trip_Stop_ID]=[Trip_Stop_Person_Activity].[Trip_Stop_ID]) WHERE ( ( ( [Trip_Stop].[Location_ID] = @LocationId2 AND [Trip_Stop].[Status_ID] <> @StatusId3) AND [Trip_Stop].[ETD] <= @Etd4)) GROUP BY [Trip_Stop_Person_Activity].[Person_ID] HAVING ( SUM([Trip_Stop_Person_Activity].[Person_Activity_type_ID] * @LLBLEP6) = @PersonActivityTypeId5)
    Parameter: @LLBLEP1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @LocationId2 : String. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "1-2".
    Parameter: @StatusId3 : String. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "Deleted".
    Parameter: @Etd4 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 16/05/2009 10:05:00.
    Parameter: @LLBLEP6 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @PersonActivityTypeId5 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.



I had to enter
Code:
TripStopPersonActivityFields.PersonActivityTypeId * 1

rather than just
Code:
TripStopPersonActivityFields.PersonActivityTypeId


to avoid "Unable to cast object of type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityField' to type 'SD.LLBLGen.Pro.ORMSupportClasses.IExpression'."

I wonder if there is a neater way around this?
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.