Using GROUP BY and HAVING clauses in an ADO Query
kbt140895
PRODUCTK2 Five
K2 blackpearl
BASED ONK2 blackpearl (all)
TAGSADO.NET Data Provider
K2 SmartObject Integration
This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice. This article is not considered official documentation for K2 software and is provided "as is" with no warranties.
Objective
Successfully execute an ADO query which has both GROUP BY and HAVING clauses.Before You Begin
An SQL stored procedure requiring an input parameter is surfaced as a SmartObject. To execute an ADO query against this SmartObject, the parameter should be specified in the HAVING clause. With the GROUP BY clause included, the SmartObject Tester will show an error suggesting that the parameter was not provided.Procedure or function '[stored_procedure]' expects parameter '@[parameter]' which was not supplied.
How-to Steps
To get the expected result, please follow the steps below:
- Execute the SmartObject with the HAVING clause to specify the parameter as a subquery.
- Use the results returned in the subquery to perform a GROUP BY.
SELECT [Properties, Aggregate Function] FROM
(SELECT * FROM [SmartObject] HAVING [Parameter]=[Value])
GROUP BY [Property]