Hi everyone,
I’m encountering an issue with my BAQ in Epicor. When I run the BAQ for a single date, it returns data for multiple companies as expected. However, when I input a date range for both companies, I receive no data, and I get the following error message:
Not that it’s not returning results the query execution is timing out and not providing anything as it has not finished its work.
Can we see your date range parameters and your table criteria?
Sure, Here they are:
I am using three different CTEs to determine the sales representative code level. These CTEs help identify if a sales representative is at level 1, 2, or 3. My criteria are applied at the subquery level for these different CTEs.
When I try to run the same query in SQL Server for the same date ranges, I get results much faster.
I don’t know the answer to this, so feel free to bash me over the head if its a stupid question… but can you apply the date range criteria in each of the CTE queries themselves? Or do you HAVE to do it at the top level?
Thank you for the suggestion! It worked by adding the date range criteria at the CTE level. I appreciate the help.
Oh… well that was a “Blind Squirrel” for the win then, because I really had no idea.
I have also addressed this issue by extending the QueryTimeOut under the Execution Settings. It simply allows additional time for the Query to complete.
Well, i agree and was going to suggest that, but it seemed like a pretty simple criteria that shouldn’t have taken that long to process.
So, even his original query may have gotten the results (given enough time)… but I was curious whether setting the criteria directly on the CTE would improve performance.
