asked
SQL Server 2012 Loop through rows to find matching record
I have records in a table (see attached Excel file) where each WONbr is listed, and it is either linked to a Project (when BuildToType = "PRJ"), or linked to another WO (MWO). For each record, I need to identify the Project the WONbr is ultimately linked to. Example: WONbr 5344AW08 is a PRJ, and linked to Project 5344AV00. This is easy, it is already linked. For the MWO's, I need to be able to loop through the table, and work my way back to the WONbr that is directly linked to the Project. Example: WONbr 5344AW24 is linked to 5344AW23 ----> 5344AW22 ----> 5344AW21 ----> 5344AW08 ----> **5344AV00** (which is the Project Nbr). What's the best way to loop thru the records until I hit the target? Also, I would like the results of each WONbr and the linked Project to be defined in a UDF that can be called by a SQL View for a report. Thanks in advance for your help.
There is no reason to use the loop here. Before entering into discussion about the possible solution, let me quote @Phil Factor who has this to say in section 7.15 of his [SQL Code Smells][1] article: > A WHILE loop is really a type of cursor. Although a WHILE loop can be useful for several inherently procedural tasks, you can usually find a better relational way of achieving the same results. The database engine is ***heavily optimized to perform set-based operations rapidly. Don’t fight it***! (bold, italics are mine) Now, let's move on to the solution. I will make some assumptions about the data based on the sample provided in the spreadsheet attached to the question post. Sadly, the values in most columns appear to have a bunch of trailing spaces, which probably means that their data type is char, not varchar. I also hope that the empty cells identify NULL rather than more cumbersome empty string. The data like this may be queried via so-called recursive CTE. This technique is frequently used in T-SQL for displaying the hierarchical data, such as bill of materials, organization's food chain, a.k.a. Org Chart, or anything else designed as adjacency list (self-referencing table). The solution assumes that the parent-most work order is linked to project directly (so the BuildToWO is null) while child/grandchild etc. work orders are linked to the work orders in the tree with unbroken branches (meaning that each work order only exists once and cannot belong to multiple branches of the same tree). The script below includes a couple of commonly used columns (Level and Sort where Sort stores the complete path of the work orders all the way up to the project). Here is the script: ;with hierarchy as ( select WONbr, cast(BuildToProj as varchar(10)) BuildToProj, 0 TheLevel, cast(BuildToProj + '/' + WONbr as varchar(max)) Sort from YourTable where BuildToProj is not null union all select t.WONbr, cast(substring(h.Sort, 1, charindex('/', h.Sort) - 1) as varchar(10)), h.TheLevel + 1, h.Sort + '/' + cast(t.WONbr as varchar(10)) from YourTable t inner join hierarchy h on t.BuildToWO = h.WONbr ) select * from hierarchy order by Sort; Based on the sample data, the script produces the following results: WONbr BuildToProj TheLevel Sort ---------- ----------- -------- ----------------------------------------------------------------- 5344AW08 5344AV00 0 5344AV00 /5344AW08 5344AW09 5344AV00 1 5344AV00 /5344AW08 /5344AW09 5344AW10 5344AV00 2 5344AV00 /5344AW08 /5344AW09 /5344AW10 5344AW11 5344AV00 3 5344AV00 /5344AW08 /5344AW09 /5344AW10 /5344AW11 5344AW12 5344AV00 3 5344AV00 /5344AW08 /5344AW09 /5344AW10 /5344AW12 5344AW13 5344AV00 3 5344AV00 /5344AW08 /5344AW09 /5344AW10 /5344AW13 5344AW14 5344AV00 2 5344AV00 /5344AW08 /5344AW09 /5344AW14 5344AW15 5344AV00 1 5344AV00 /5344AW08 /5344AW15 5344AW16 5344AV00 2 5344AV00 /5344AW08 /5344AW15 /5344AW16 5344AW17 5344AV00 2 5344AV00 /5344AW08 /5344AW15 /5344AW17 5344AW18 5344AV00 2 5344AV00 /5344AW08 /5344AW15 /5344AW18 5344AW19 5344AV00 1 5344AV00 /5344AW08 /5344AW19 5344AW20 5344AV00 1 5344AV00 /5344AW08 /5344AW20 5344AW21 5344AV00 1 5344AV00 /5344AW08 /5344AW21 5344AW22 5344AV00 2 5344AV00 /5344AW08 /5344AW21 /5344AW22 5344AW23 5344AV00 3 5344AV00 /5344AW08 /5344AW21 /5344AW22 /5344AW23 5344AW24 5344AV00 4 5344AV00 /5344AW08 /5344AW21 /5344AW22 /5344AW23 /5344AW24 All those extra spaces in the Sort are the consequences of the CHAR data type which causes trailing spaces for all work order/project values. Trim them if necessary. Hope this helps. Oleg [1]:
https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-code-smells/
Oleg, Thanks, you definitely put me on the right track. I have used CTE's many times in the past, but not recursive CTE's. While the table in question does not allow NULL values (it's an ERP system design that cannot be changed), I was able to modify your sample to get it to work for what I need. Thanks for the lesson!!
@mrecpa I am glad I was able to help. Getting the answer accepted/upvoted would be nice, but oh well. For optimal performance, the first 2 rules to follow are these: Never allow non-sargable arguments in the join condition of recursive CTE, i.e. casting of any kind, functions should not be used. For example: ***on cast(t.BuildToWO as varchar) = rtrim(h.WONbr)*** instead of ***on t.BuildToWO = h.WONbr*** will instantly kill performance. The recursive CTE will definitely benefit from the non-clustered index on parent ID, ID (in this order). In your case, this would be on BuildToWO, WONbr. For alternative solutions to adjacency list design, please read [Hierarchies on Steroids #1][1] and [Hierarchies on Steroids #2][2] by @Jeff Moden. There is a lot to learn from these articles! [1]:
http://www.sqlservercentral.com/articles/Hierarchy/94040/ [2]:
http://www.sqlservercentral.com/articles/T-SQL/94570/
18 People are following this question.
