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?
I’ve used a CTE before to combine data from two tables into one view.
So the column would read “Order/Quote” and would be populated by both.
I learned it at Insights a few years back, so I would have to dig up the example if you wanted it.
I’d be interested to see it. From what I’ve seen, I haven’t found an example that couldn’t be done with a sub or a Union. (other than the hierarchies).
So I found the example, and made the query in the instructions, and like I suspected, was able to change the order, and add some () in the subquery list and it works the same with just unions. Same results. It’s maybe a little easier with the CTE , and the SQL is definitely easier to read. But as far as the editor goes, not required.
I have used CTEs as a looping tally-incrementer like this in the past.
Here's a sample of SQL code:
--Gives a row for each day in the date range by iterating through days
DECLARE @StartDate DATETIME = '1/1/2019';
DECLARE @EndDate DATETIME = '3/1/2019';
WITH DayRows AS
(
SELECT @StartDate DayStep
UNION ALL
SELECT DATEADD(DAY, 1, DayStep)
FROM DayRows
WHERE DayStep < @EndDate
)
SELECT DayStep FROM DayRows;
How about…
Getting the Order Info for the “Last” Order a customer placed for all Customers.
That is a List of Customers , with the order info Total, Order Date etc… for the “Latest” order info bit. I think you’d need a CTE for this or at least a VERY complex other query.
SELECT c.CustID, o.* FROM Erp.Customer c
CROSS APPLY
(SELECT TOP 1 h.OrderNum,h.OrderDate,SUM(d.OrderQty*d.UnitPrice) Total
FROM Erp.OrderHed h
INNER JOIN Erp.OrderDtl d ON h.Company=d.Company AND h.OrderNum=d.OrderNum
WHERE h.CustNum = c.CustNum
GROUP BY h.OrderNum,h.OrderDate ORDER BY h.OrderDate DESC) o
In all seriousness, I think CTE has a good place in everyone’s SQL toolbelt.
I prefer to write stored procedures where I can to return complex data due to the ability to create indexes on temporary tables while aggregating different datasets, but in certain circumstances and where stored procedures are not possible, I might use a CTE.
So I’ve looked at a lot of those, and most of it reads basically, “nice to replace subqueries for maintenance and readability”. Which in native SQL makes a lot of sense. In Epicor, since it handles all of the subquery stuff for you, maintenance and readability (if you don’t care about the code on the general tab) are a non-issue.
This is the one statement that has be thinking that there is something besides the recursive part that it allows that an inner subquery does not.
or performing GROUP BY using non-deterministic functions…
But I don’t really know what that means, or have an example of from within the BAQ editor.
A deterministic function is a SQL function where the output is the same every time as long as the data is the same. For instance LEFT(Field,2) will always give you the same result if Field is the same.
A non-deterministic function is a SQL function where you do not know in advance what the result will be based on the the machine state, such as NEWID().
You might decide to group by a derivative of GETDATE() or something in a CTE.
Do be careful how many CTEs you string together in a single select/update/delete. I’ve found more than 3 can severely impact performance, as the query plan/optimizer loses the ability to understand what the intent was.
