Maximum recursion 100 has been exhausted before statement completion

I’ve created a CTE to get all dates from the first day of the year to the last day of the year. When I limit the date range to less than 100 days (April 10) then it runs fine. When I set the limit to 365 days (December 31) it reports "maximum recursion 100 has been exhausted before statement completion" and stops running. I’ve read that I have to set "option (maxrecursion 0)" but I can’t find anywhere to set that option. I’ve tried reading through the application help and looking through the execution settings. If there is no option for this then I’ll just make 4 sub-queries to get the first 100, second 100, etc.

Final result


Union All Criteria

Union All Calculated Field

Top Level Query


If anyone wants to use my work-around, I just made 4 CTEs, that each started at a DATEADD interval of 100 and had a criteria that was less than that date plus 100 days, other than the last CTE which was a criteria of less than 365. I then used the formula below to join them into one field.

Why are all your screenshots so yellow toned? :o - Vintage Effect

1 Like

Looks like an old tube monitor and he is taking screenshots on his flip phone :wink:

FYI - Grabbing a guru in this area to discuss but he is sleeping (He is in Moscow)

1 Like

I can just imagine the conspiracy theories that are going to build from this thread, but to sadly end any discussion, I left F.lux on.


Interesting, I didnt know f.lux manipulates your screenshots, I guess its merely just a full screen overlay… I never knew that; I always assumed it messed with the Color Profile of your monitor instead.

1 Like

Your workaround is the only safe and correct solution currently.
I can offer a hack to get the query tuning option into resulting sql statement but I can’t guarantee this will work in future versions.
Add a condition to the top level subquery criteria which is always true. It should compose into this:
Note, the condition is the last statement in the query. It is always true and doesn’t affect query output but let us inject the option statement to the end of the query.
Of course you are prohibited from using malicious sql instructions here, BAQ Designer will stop you if you try to drop the database via this loophole for example.


I’m new user here so I’m allowed to use one image per message.
Here is screenshot of condition setting:


For those who are not aware, Dmitry is the lead on BAQ Development among other activities and roles. I finally drug him up here to engage with you fine folks. Be gentle on him for a bit before harassing him too much :wink:



Welcome @Dmitry_Kashulin - we’re glad to get any Epicor pros we can get our hands on! And thank you to @Bart_Elia as always.


My job is easy - show off our talent and make introductions.


more than welcome to another top expert guy in our forum @Dmitry_Kashulin, and many thanks for all of you
and all participated members


Bumped up your trust level @Dmitry_Kashulin that should fix the image issues.

Thanks for your help, and welcome!

1 Like

I’m marking your answer as the solution for now and will test it out later. But that is exactly what I was looking for, thank you.

Will this work for other query hints as well like OPTION (MAXDOP 1)?

Also, any chance we can get the BAQ’s to run with STATISTICS IO ON?

Welcome @Dmitry_Kashulin

Sure, just list desired options delimited by comma like this:

There is no way to add preambular part to a query sql statement via BAQ Designer.

Thanks so much!

Is there any chance we could get Scan Count, Logical Reads, and Physical Reads added to the Query Execution Message along with the Query execution total time? It would be incredibly helpful in quickly diagnosing bad BAQ’s.

1 Like