Could not calculate the query count
Question

I have a Query that only gives me this error in runtime saying i could not calculate the query count. After some research i found this bit n the forum:

"The SQL query '<SQL query>' must be a SELECT statement "


Since my Query starts as :  With X as{...} Select ...(subquery) and so on.

It works when testing, but on runtime it's not. Any ideia?
Thanks in advance

Hi Nuno,

Can you please share your query. Query some thing below is working fine 

with cterc as(SELECT COUNT(*) as rn
FROM {Materials}
UNION ALL
SELECT COUNT(*)
FROM {Material2}
)
 SELECT SUM(rn) as totalrowNo
 from cterc

HI Devendra the query is the following

With T as 
    (
    Select 
        {TraceContent}.[Id], {TraceContent}.[CreatedOn], {TraceContent}.[TraceTypeId], {TraceContent}.[WebReferenceName]       
    From 
        {TraceContent}
    where
        {TraceContent}.[CreatedOn] >= @TraceContents_From  
        and {TraceContent}.[CreatedOn] <= @TraceContents_Until 
        and ({TraceContent}.[TraceTypeId]= @TraceTypeID and {TraceContent}.[TraceTypeId] IS NOT NULL)
             
    )
    Select 
        T.[Id],T.[CreatedOn],T.[TraceTypeId],T.[WebReferenceName],
        (Select 
            {TraceContentAdditionalInfo}.[Value] from {TraceContentAdditionalInfo} 
         where 
            {TraceContentAdditionalInfo}.[Name]= @CaseRepositoryID and T.[Id]={TraceContentAdditionalInfo}.[TraceContentId]
        ) As CaseRepositoryID,
        (Select 
            {TraceContentAdditionalInfo}.[Value] from {TraceContentAdditionalInfo} 
         where 
            {TraceContentAdditionalInfo}.[Name] = @CaseStatus and T.[Id]={TraceContentAdditionalInfo}.[TraceContentId] 
        ) As CaseStatus  
From T  

The error occurs when executing the query altough,outside of this, when using the list navigation it displays this warning, so the platform already realizes something is missing with the query.

Devedra i have another question about this topic using your example

with cterc as(SELECT COUNT(*) as rn
FROM {Materials}
UNION ALL
SELECT COUNT(*)
FROM {Material2}
)
 SELECT SUM(rn) as totalrowNo
 from cterc

If i wanted to do a where clause using that totalrowNo how should i call it?


"Where cterc.totalrowNo=.... ? because i'm having trouble recognizing the alias in the where clause.

Hi Nuno,

Try like below.

with cterc  as (SELECT {Materials}.[Attribute1] as rn
FROM {Materials}
UNION ALL
SELECT {Material2}.[Attribute1]
FROM {Material2}
)

select * from cterc where cterc.rn = 333;

Best Regards

Devendra




Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.