- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Group by clause
Joined: 17-May-2011
How to execute following query in Self servicing
Select COUNT(*) from ( Select (Case when Status='c' and Clients.ConverisonDate <'2012-07-24 14:04:37.653' then 'Clients' when Status in ('n','l') OR Status='C' and ConverisonDate >= '2012-07-24 14:04:37.653' then 'Leads' else Null end ) ClientStatus from Clients) Cal Group by Cal.ClientStatus having Cal.ClientStatus is not null
Or query like this :
Select Count(clientId) as Count, (Case when Status='c' and Clients.ConverisonDate <'2012-07-24 14:04:37.653' then 'Clients' when Status in ('n','l') OR Status='C' and ConverisonDate >= '2012-07-24 14:04:37.653' then 'Leads' else Null end ) ClientStatus from Clients group by (Case when Status='c' and Clients.ConverisonDate <'2012-07-24 14:04:37.653' then 'Clients' when Status in ('n','l') OR Status='C' and ConverisonDate >= '2012-07-24 14:04:37.653' then 'Leads' else Null end)
Joined: 17-May-2011
I tried out the following thing:
TypedListDAO oTypedListDAO = new TypedListDAO();
ResultsetFields oResultsetFields = new ResultsetFields(2);
ClientLeadContactedInfo oClientLeadContactedInfo = new ClientLeadContactedInfo();
EntityField oEntityField = new EntityField("ClientStatus", new DbFunctionCall("Case when {0}={1} and {2} <{3} then 'Clients' when {0} in ({4},{5}) then 'Leads' when {0}={1} and {2} >= {3} then 'Converted Client' else Null end",
new object[] {ClientFields.Status,Utility.GetStringValue(ClientsEnum.ClientStatus.Client),
ClientFields.ConverisonDate,startDate, Utility.GetStringValue(ClientsEnum.ClientStatus.NewLead),
Utility.GetStringValue(ClientsEnum.ClientStatus.ClientLead)}), typeof(string));
oResultsetFields.DefineField(ClientFields.ClientId, 0, "Count", AggregateFunction.CountDistinct);
oResultsetFields.DefineField(oEntityField, 1);
GroupByCollection oGroupByCollection = new GroupByCollection(oResultsetFields[1]);
IPredicateExpression oPredicateExpressionClient = new PredicateExpression();
oPredicateExpressionClient.Add(ClientFields.IsDeleted == false);
if (matchmakerId != null)
{
IPredicateExpression oPredicateExpression = new PredicateExpression();
oPredicateExpression.Add(ClientMatchMakerFields.MatchMakerId == matchmakerId.Value);
oPredicateExpression.AddWithAnd(new FieldBetweenPredicate(ClientMatchMakerFields.Date, startDate, endDate));
oPredicateExpressionClient.AddWithAnd(new FieldCompareSetPredicate(ClientFields.ClientId, ClientMatchMakerFields.ClientId, SetOperator.In, oPredicateExpression));
}
IDataReader drTotal = oTypedListDAO.GetAsDataReader(null, oResultsetFields, oPredicateExpressionClient, null, CommandBehavior.CloseConnection, 0, null, oGroupByCollection, true, 1, 3);
But I am receiving following error:
An exception was caught during the execution of a retrieval query: Column 'Old.dbo.Clients.Status' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.ConverisonDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.ConverisonDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Kindly help me out
Joined: 17-May-2011
My regrets for the wrong information provided this is my latest code:
TypedListDAO oTypedListDAO = new TypedListDAO();
ResultsetFields oResultsetFields = new ResultsetFields(2);
ClientLeadContactedInfo oClientLeadContactedInfo = new ClientLeadContactedInfo();
EntityField oEntityField = new EntityField("ClientStatus", new DbFunctionCall("Case when {0}={1} and {2} <{3} then 'Clients' when {0} in ({4},{5}) then 'Leads' when {0}={1} and {2} >= {3} then 'ConvertedClient' else Null end",
new object[] {ClientFields.Status,Utility.GetStringValue(ClientsEnum.ClientStatus.Client),
ClientFields.ConverisonDate,startDate, Utility.GetStringValue(ClientsEnum.ClientStatus.NewLead),
Utility.GetStringValue(ClientsEnum.ClientStatus.ClientLead)}), typeof(string));
oResultsetFields.DefineField(ClientFields.ClientId.SetAggregateFunction(AggregateFunction.Count), 0);
oResultsetFields.DefineField(oEntityField, 1);
IPredicateExpression oPredicateExpressionClient = new PredicateExpression();
oPredicateExpressionClient.Add(ClientFields.IsDeleted == false);
oPredicateExpressionClient.AddWithAnd(oEntityField != DBNull.Value);
if (matchmakerId != null)
{
IPredicateExpression oPredicateExpression = new PredicateExpression();
oPredicateExpression.Add(ClientMatchMakerFields.MatchMakerId == matchmakerId.Value);
oPredicateExpression.AddWithAnd(new FieldBetweenPredicate(ClientMatchMakerFields.Date, startDate, endDate));
oPredicateExpressionClient.AddWithAnd(new FieldCompareSetPredicate(ClientFields.ClientId, ClientMatchMakerFields.ClientId, SetOperator.In, oPredicateExpression));
}
GroupByCollection oGroupByCollection = new GroupByCollection(oResultsetFields[1]);
IDataReader drTotal = oTypedListDAO.GetAsDataReader(null, oResultsetFields, oPredicateExpressionClient, null, CommandBehavior.CloseConnection, 0, null, oGroupByCollection, true, 0, 0);
and this the error I am getting:
An exception was caught during the execution of a retrieval query: Column 'Old.dbo.Clients.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.ConverisonDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'Old.dbo.Clients.ConverisonDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Joined: 17-May-2011
Joined: 21-Aug-2005
Could you please capture the generated SQL Query, and try executing it manually against the database (for example using SQL Management Studio). This should guide you to what is going wrong, exactly. And then you can find out how to fix the row SQL and hence the code using the LLBLGen framework.
To capture the generated SQL add the following to the application config file:
<system.diagnostics>
<switches>
<add name="SqlServerDQE" value="4" />
</switches>
</system.diagnostics>
ref: Troubleshooting And run the application in Debug mode, and the SQL would appear in Visual Studio's Output window.
Joined: 17-May-2011
Hi there, Following is the extracted generated sql
Select Count(ClientId) as ClientCount, (Case when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] <'7/22/2012 6:30:00 PM' then 'Clients' when [Old].[dbo].[Clients].[Status] in ('n','l') then 'Leads' when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] >= '7/22/2012 6:30:00 PM' then 'ConvertedClient' else Null end) ClientStatus from Clients where ( [Old].[dbo].[Clients].[IsDeleted] = 0 AND Case when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] <'7/22/2012 6:30:00 PM' then 'Clients' when [Old].[dbo].[Clients].[Status] in ('n','l') then 'Leads' when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] >= '7/22/2012 6:30:00 PM' then 'ConvertedClient' else Null end IS NOT NULL AND [Old].[dbo].[Clients].[ClientID] IN (SELECT [Old].[dbo].[Client_MatchMaker].[ClientID] AS [ClientId] FROM [Old].[dbo].[Client_MatchMaker] WHERE ( [Old].[dbo].[Client_MatchMaker].[MatchMakerID] = 4 AND [Old].[dbo].[Client_MatchMaker].[Date] BETWEEN '7/22/2012 6:30:00 PM' AND '7/29/2012 6:29:59 PM'))) Group by Case when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] <'7/22/2012 6:30:00 PM' then 'Clients' when [Old].[dbo].[Clients].[Status] in ('n','l') then 'Leads' when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] >= '7/22/2012 6:30:00 PM' then 'ConvertedClient' else Null end
The above query executes in the database successfully. I further dug out for getting the same error in the database and was surprised to see it is actually not executing the group by clause it actually executes only following part of query:
Select Count(ClientId) as ClientCount, (Case when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] <'7/22/2012 6:30:00 PM' then 'Clients' when [Old].[dbo].[Clients].[Status] in ('n','l') then 'Leads' when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] >= '7/22/2012 6:30:00 PM' then 'ConvertedClient' else Null end) ClientStatus from Clients where ( [Old].[dbo].[Clients].[IsDeleted] = 0 AND Case when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] <'7/22/2012 6:30:00 PM' then 'Clients' when [Old].[dbo].[Clients].[Status] in ('n','l') then 'Leads' when [Old].[dbo].[Clients].[Status]='c' and [Old].[dbo].[Clients].[ConverisonDate] >= '7/22/2012 6:30:00 PM' then 'ConvertedClient' else Null end IS NOT NULL AND [Old].[dbo].[Clients].[ClientID] IN (SELECT [Old].[dbo].[Client_MatchMaker].[ClientID] AS [ClientId] FROM [Old].[dbo].[Client_MatchMaker] WHERE ( [Old].[dbo].[Client_MatchMaker].[MatchMakerID] = 4 AND [Old].[dbo].[Client_MatchMaker].[Date] BETWEEN '7/22/2012 6:30:00 PM' AND '7/29/2012 6:29:59 PM')))
And I am totally clueless why is this happening
Joined: 17-Aug-2003
Keep in mind that we don't need a database with data, nor big pieces of code. The reprocase has to be small.
btw, when you enable tracing, it will also log sub queries which are embedded into other queries.
To test what you do, create a unit test, add the code there and run that test alone, then check what queries are logged. Try to minimize your code till it runs into the problem.
But I have a hard time what the problem really is, as it seems you did create code to create the full query but the last post suggests your query now doesn't work, but what it doesn't do is unclear.