Advance Query "Could Not Calculate The Query Count"

Advance Query "Could Not Calculate The Query Count"

  
I've got and Advanced Query using an IF statement (see below). It is working fine and returning correct results when I test it in the SQL dialog box but when I run the application I get an internal error with the following message in Debug "Could not calculate the query count." I'm assuming this is because of the way I am using the IF statement. Has anyone run into this problem or do you possibly see a way I can rewrite the SQL to work with the Count function?
IF (@TranslationFilter > 0)
BEGIN
    SELECT {Text}.[TextId], {Text}.[Text], {Translation}.[Translation], {Translation}.[TranslationId]
    FROM {Text}
    LEFT OUTER JOIN {Translation} ON {Text}.[TextId] = {Translation}.[TextId]
    AND {Translation}.[LanguageId] = @LanguageId
    WHERE Translation.TextId IS NULL
    ORDER BY {Text}.[TextId]
END

ELSE
BEGIN
    SELECT {Text}.[TextId], {Text}.[Text], {Translation}.[Translation], {Translation}.[TranslationId]
    FROM {Text}
    LEFT OUTER JOIN {Translation} ON {Text}.[TextId] = {Translation}.[TextId]
    AND {Translation}.[LanguageId] = @LanguageId
    ORDER BY {Text}.[TextId]
END
Hi Tim,

Looking at your query, and considering you are not using @TranslationFilter anywhere in it, why not rewrite it to

   SELECT {Text}.[TextId], {Text}.[Text], {Translation}.[Translation], {Translation}.[TranslationId]
   FROM {Text}
   LEFT OUTER JOIN {Translation} ON {Text}.[TextId] = {Translation}.[TextId]
   AND {Translation}.[LanguageId] = @LanguageId
   WHERE @TranslationFilter = 0 OR Translation.TextId IS NULL
   ORDER BY {Text}.[TextId]

Also, note that the Count property is to be used exclusively for pagination.
Use CurrentRowNumber if you just want to know the returned rows count.

Regards,
João Rosado
Thanks for the reply Joao,

TranslationFilter is a parameter being set from a combo box on my page. The user sets the combo box if they want to see all results where TextId is null. Otherwise it shows all normal results like in my ELSE. Hence, combining them into one query won't work as you wrote it. 

I originally used two separate Advance Queries but was having some trouble getting them to affect the same Table on the screen so I thought it might be easier to combine them into one. 

Tim

hi Tim,

You are right the query thet you wrote and the query that joão wrote are not the same. but if you use this one It should be

   SELECT {Text}.[TextId], {Text}.[Text], {Translation}.[Translation], {Translation}.[TranslationId]
   FROM {Text}
   LEFT OUTER JOIN {Translation} ON {Text}.[TextId] = {Translation}.[TextId]
   AND {Translation}.[LanguageId] = @LanguageId
   WHERE (@TranslationFilter > 0 AND Translation.TextId IS NULL) OR @TranslationFilter <=0
   ORDER BY {Text}.[TextId]

Please notice that Current row number and the count are not the same specialy if you have the max records set in the advance query.

Regards,
Carlos Rocha
Thank you man. That query works great.
You are the man!