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)

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

I can't see the Having object set in the code snippet you've posted. Did you set the "oGroupByCollection.Having" ?

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.

Please help me in digging the issue

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.

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

Strange. Could you please provide a repro solution? Better on Northwind, otherwise please attach the database script. And if this is confidential, please create a HelpDesk thread (that's private).

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.