how to loop without endless joins

Question

how to loop without endless joins

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jeff Moden (2/24/2016)
First of all, apologies to Lynn Pettis. He's spot on for the solution to this problem but I missed looking at his code. Nice job for realizing that 1) this was a hierarchical problem and 2) realizing that a "Sort Key" is a must. Unfortunately, if you add another Level 3 and 4 with the "right" numbering for the Appl, the new Level 4 doesn't follow the correct hierarchical order according to the parent/child relationship in the hierarchy.
Here's the code with the added nodes.
create table #mynumbers (Appl int, Pre_App int);
insert into #mynumbers (Appl, Pre_App) values (58672235, 0);
insert into #mynumbers (Appl, Pre_App) values (58791134, 58672235);
insert into #mynumbers (Appl, Pre_App) values (58800760, 58791134);
insert into #mynumbers (Appl, Pre_App) values (58800761, 58791134); --Added as another level 3
insert into #mynumbers (Appl, Pre_App) values (58800000, 58800760); --Added as a level 4, ends in wrong place for Lynn's
insert into #mynumbers (Appl, Pre_App) values (58993700, 0);
insert into #mynumbers (Appl, Pre_App) values (59068028, 58993700);
insert into #mynumbers (Appl, Pre_App) values (59139976, 59068028);
insert into #mynumbers (Appl, Pre_App) values (59200408, 59139976);
The code below works in a similar manner except I store the "child" ID as a concatenated binary path, which solves the correct order problem and sets us up for some other magic that I'll explain in a minute. I also threw in the "Hierarchical Level" because 1) almost everyone eventually wants to know the value and 2) it's also important to that other magic. Here's the code.
WITH cteTraverse AS
( --=== Find the root nodes and use them for the start of the SortPath
SELECT Appl
,Pre_App
,hLevel = 1
,SortPath = CAST(CAST(Appl AS BINARY(4)) AS VARBINARY(1000))
FROM #mynumbers
WHERE Pre_App = 0
UNION ALL
--==== Continue the traversal of the hierarchy and concatenate each level to the previous in SortPath
SELECT tbl.Appl
,tbl.Pre_App
,hLevel = cte.hLevel+1
,SortPath = CAST(cte.SortPath + CAST(tbl.Appl AS BINARY(4)) AS VARBINARY(1000))
FROM cteTraverse AS cte
JOIN #mynumbers AS tbl
ON tbl.Pre_App = cte.Appl
)
SELECT *
,Original_Appl = CAST(SUBSTRING(SortPath,1,4) AS INT) --Extracts the IDs at Level 1
FROM cteTraverse
ORDER BY SortPath
;
Of course, you can remove the SortPath and Hierarchical Level columns from the final SELECT list to meet the original requirements but let's get to the "magic"...
Most hierarchies change rather infrequently but are read from a lot. Rather than constantly and unnecessarily burning clock cycles and extra read re-traversing the hierarchy every time you want to read the hierarchy, you can build "Nested Sets" which afford very high speed, low resource usage methods for returning hierarchical data. Now, don't be fooled into getting rid of the original "Adjacency List" (parent/child structure) because Adjacency Lists are super easy to maintain. We'll just rebuild the Nested Sets when there's a change.
I'll also tell you not to be fooled into using a bloody "push stack" method, which requires an ID stack, a bazillion reads, and a slow While Loop that needs to do too much for it's own good. Instead, please see the method explained in the following article.
http://www.sqlservercentral.com/articles/Hierarchy/94040/
Now, if this hierarchy needs to be aggregated for dollar amounts or what have you (obviously, can't tell from the data given), then you need to see the following article, as well.
http://www.sqlservercentral.com/articles/T-SQL/94570/
Last but not least, hat's off to astrid 69000 for posting readily consumable data. It saves us a whole lot of time and makes things instantly clear. Well done!

I'll agree, my solution here was simplistic. I have done this for our lookups where the "data error" Jeff inserted would not be a problem. Of course I have run into other issues with our lookups, like the parent/child links being wrong.

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

Hi,

Sorry it took me long to answer.

First of all, thanks to everyone who took the time to help.

I have read and part I did understand, part I didn't.

Lynn Pettis solution worked on my table, the other solution didn't work for me. it was giving me some crazy number when I ran the whole table.

Even though I have to say, I am not sure cause I did fully understand it, so maybe I just didn't adjust it properly.

I don't understand why do you make a SortPath as CAST(CAST(Appl AS BINARY(4)) AS VARBINARY(1000))

Thanks

Astrid

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

He changed the data type to allow extra space for the concatenation to occur. Try changing both VARBINARY(1000) to a BINARY(4) and run it to see the difference.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Ed Wagner (3/7/2016)
He changed the data type to allow extra space for the concatenation to occur. Try changing both VARBINARY(1000) to a BINARY(4) and run it to see the difference.

... where it will, of course, fail. 😛 (Just need to make sure the OP knows that).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

astrid 69000 (3/7/2016)
Hi,
Sorry it took me long to answer.
First of all, thanks to everyone who took the time to help.
I have read and part I did understand, part I didn't.
Lynn Pettis solution worked on my table, the other solution didn't work for me. it was giving me some crazy number when I ran the whole table.
Even though I have to say, I am not sure cause I did fully understand it, so maybe I just didn't adjust it properly.
I don't understand why do you make a SortPath as CAST(CAST(Appl AS BINARY(4)) AS VARBINARY(1000))
Thanks
Astrid

Ed is correct there. The conversion to VARBINARY(1000) leaves room for 250 levels in the Hierarchy. The reason why we have to do that same conversion in the CTE after the UNION ALL is to preserve the datatype though out the recursive CTE. The concatenation makes the datatype different and an explicit conversion is necessary to keep the datatype the same as it is in the first part of the CTE.

Did you read the link I posted? It's a bit long but it explains all of this. Here's the link again...

http://www.sqlservercentral.com/articles/Hierarchy/94040/

You've selected Lynn's code as the answer but even he recognizes the fault in the code (and he posted that he agrees), it has the potential for silent logical failures under certain fairly common conditions that don't exist in the original test data. Without any disrespect towards him (he's one of the heavy hitters on this site and you've GOT to respect someone like that agrees there's a potential fault in their code), I strongly recommend that you don't use his posted code because of the silent logical faults that will occur if the underlying data isn't absolutely in the right logical order to begin with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 6

I will try again. And read the article you sent (apparently I missed it)

thanks!!!!!