Could not calculate the query count

Could not calculate the query count

Hi All,

I have an advanced query that is using 2 cte's and a select statement. It runs fine while testing the advanced query but while executing it from the page it is throwing exception with message"Could not calculate the query count" . What am I missing ? 


The Queries (both Advanced Query and Simple Query) return two different variables: Query.List and Query.Count

The first thing you need to know is that each variable is the result of a different query. The query.List is the result of the query you have specified inside the advanced query, and the Query.Count is the result:
  1. For simple queries replacing the Select attribute1,...,attributen by a Select count(1);
  2. For advanced queries it is a result of creating a cursor over your query and finding how many rows the cursor can iterate.
This means that using Query.List.Length is different than Query.Count. In fact this is a common source of errors and performance problems.
See more information in Service Studio help.

Another thing you should know is that Service Studio makes optimizations, so if you never use the Query.Count that query will never run. The same applies to the Query.List: if you make a query in Preparation but don't use it anywhere in your screen, then que query never runs.

Now lets address your problem..
Remember what I've said about the Query.Count? Probably if your query is specified in such a way that a cursor cannot be created, then the query will misbehave and return an error like the one you are seeing.

A way to solve this is (and assuming you only are interested in knowing the count and not having the result set) is to change your query to do the count itself.
Just to add some information about this issue, a practical case where this error can also occur is on CTEs.
When you build one, you can't use the Query.Count since the count can't be calculated because of the same mentioned problem (see the nice explanation given by Joao Fernandes).

If you're going to use the query results you can simply use Query.List.Length as a substitute for Query.Count.

This will return Min( query.count, query.max_records), so be weary when you specify max_records and use this to know how many rows were returned.
./bump, suddenly I am encountering this as well.(after upgrade to 9.1.301.0)

I don't use .count anymore (it was for the tablerecords) but the error still occurs.

Is it on an advanced query or an aggregate?
advanced query

Mmm, odd (though it makes more sense with an Advance Query). Did this spontaneously happen after the upgrade, while working before? Are there any clues in the error logging that may point to the culprit? If not, do you have the means to check what queries are sent to the database?
Not sure,

It used to work (developer excuse #1)
I know what query I do :)

I would assume that from the error logging you'd be able to pinpoint at what stage in the execution the Count is attempted. And if everything fails, inspect the C# code, of course :).


encountering it again.

test-query works fine.

yet, when running it in screen it goes boom.

my query:

SyntaxEditor Code Snippet

WITH LastResults AS (
SELECT {FeatureRunResult}.[FeatureId] fid
        , {FeatureRunResult}.[RunDateTime] cdt
        , {FeatureRunResult}.[FeatureRunResultStatusId] frs
    FROM {FeatureRunResult}
    WHERE {FeatureRunResult}.[Id] IN 
    (  SELECT MAX({FeatureRunResult}.[Id]) id
        FROM {FeatureRunResult}
        GROUP BY {FeatureRunResult}.[FeatureId]

, {Application}.[Name]
, SUM(CASE WHEN LastResults.frs = @Passed THEN 1 ELSE 0 END)
, SUM(CASE WHEN LastResults.fid is null or LastResults.frs = @Passed THEN 0 ELSE 1 END)
, SUM(CASE WHEN LastResults.fid is null THEN 0 ELSE 1 END)
LEFT JOIN {Feature} ON ({Feature}.[ApplicationId]= {Application}.[Id])
LEFT JOIN LastResults ON ({Feature}.[Id] = LastResults.fid)

GROUP BY {Application}.[Id], {Application}.[Name], {Application}.[IsActive]
order by {Application}.[Name]

Not anxious to rewrite it, but if anyone can provide me some pointers, please :)

Iirc, Advanced Queries with a WITH can't be COUNTed by the Platform. A Test Query doesn't create a count query I think, so I don't find it strange that that works. So there must be a .Count on the query, somewhere, either explicitly or implicitely.


In runtime, the platform encapsulates the advanced query like below to count the number of rows:

SELECT COUNT(1) FROM (<advanced query>)

As Kilian said, this won't work for advanced queries that include a WITH clause unfortunately.