So I am going to be doing a presentation at the Minnesota EUG meeting next week about BAQ’s. I’m doing some research on CTE’s and see that in native SQL, they can be helpful in writing queries mostly because it packages up the subquery nicely and you can refer to the temp table easily in your code. Basically replacing an inner sub query.
What I am finding so far in the BAQ writer in Epicor, is that since Epicor is managing inner subqueries the same way as a CTE, so the CTE’s don’t seem to make any difference with regard to UI in comparison with an inner subquery. I was thinking that there may be some aggregate functions or criteria that would act differently, but so far, everything I’ve tried has worked the same in a CTE or an inner subquery.
So, does anyone have any example of a use of a CTE besides hierarchical use?