Contents tagged with CTE
today I want to write a note about interesting case I faced recently.
I was asked to load from Acumatica database Sales orders joined with PO Receipts by inventory, but I had one issue. It was needed to join only by latest PO Receipt, not by allo PO Receipts. In other words it was needed to take only one element of each group.
As usually such kind of implementation is done with help of overloading delegate, which loads all data from SQL Server into memory and then does grouping operaions in memory. Such approach is workable on relatively small cases, but if database of customer
is around 130 Gb, then we faced timeout issue.
How to deal with it.
After some trial … more
today I want to write some notes about hierarchy research in ms sql database.
Imagine you have following structure in your table
id | rootID | some additional fields
1 | 1 | ----
2 | 1 | ----
3 | 2 | ----
4 | 2 | ----
5 | 2 | ----
and image that you need to get info like who is below 1.
for this purpose following sql can be useful:
-- wrap up tree selection into tvf
create function HierarchyFunction(@ … more