February 4, 2016 at 8:49 am
WITH CTE(col1, col2, col3, Hier) AS
(
SELECT T.col1
,T.col2
,T.col3
,CAST('/'+ LTRIM(T.col2) +'/' AS VARCHAR(MAX))
FROM Table T
WHERE T.col2 = 1
UNION ALL
SELECT T.col1
,T.col2
,T.col3
,Hier + CAST(T.col2 AS VARCHAR(MAX)) +'/'
FROM Table T INNER JOIN CTE ON T.col3 = CTE.col3
WHERE CTE.Hier NOT LIKE '%/' + CAST(T.col2 AS VARCHAR(MAX)) + '/%'
)
Hi all,
I am having an issue with the above in that it goes into an infinite loop. Basically my dataset conatins many data loops and the usual heirarchy check does not work as detailed below.
I thought about using a table variable to store entries then checking on it in the recursive query section but cannot find a way to insert into said table variable from inside the recursive query.
I also have considered functions and re-writing in C# but thought there must surely be a away of doing it here.
Any ideas / solutions would be fantastic.
==== Why it doesn't work ====
There is a good 20 plus recursions in my query, below is a brief example extract
T.col1T.col2T.col3Hier
1 2 3 /2/
1 2 4 /2/
1 2 5 /2/
2 3 3 /2/3/
>>>> Later in the query
56 57 3 /2/3/5/6/32/34/36/47/48/50/57
56 57 3 /2/3/5/6/32/34/36/47/48/50/52/57
The bottom row is identical to the one above apart from the Hier column and it appears because entry 1 has a T.col3 value of 3 so it is joined and T.col2 of the latest record has a value of 57 which does not appear in the Hier of entry 1 thus it is added and as this will always be the case rows are continuously added an the query continues to run.
Thank you for taking the time to review this post any ideas would be appreciated. 🙂
February 4, 2016 at 9:20 am
I'm not sure what you're trying to do. You're not really using a hierarchy, but concatenating values.
Maybe this could work better for you.
CREATE TABLE #Table(
col1 int,
col2 int,
col3 int
);
INSERT INTO #Table
VALUES
(1,1,3),
(1,2,3),
(1,2,4),
(1,2,5),
(2,3,3);
SELECT T.col1
,T.col2
,T.col3
,( SELECT '/'+ LTRIM(i.col2)
FROM #Table i
WHERE T.col3 = i.col3
AND T.col2 >= i.col2
ORDER BY col2
FOR XML PATH(''),TYPE).value( './text()[1]', 'VARCHAR(MAX)') + '/'
FROM #Table T;
GO
DROP TABLE #Table
If that's not what you're trying to do, please post sample data the way I included it in the example.
February 4, 2016 at 9:45 am
Hi,
Thanks for taking a look at this, what I am trying to do is start with an anchor dataset such as the following provided by the user (1).
1 2 3
1 2 4
Then find any other entries in the table with for example the col3 value 4 e.g.
6 7 4
6 7 8
6 7 22
Then recursively search through its entries for example the col3 value 4, 8, 22 e.g. the standard sort of recursive query but as I said my dataset isn't linear for example a management hierarchy but can loop multiple times as as I explained earlier I get stuck in a infinite loop which I am unable to break.
Sadly I cannot post a dataset as it encompasses thousands of records.
Hopefully this is enough information.
Thanks again for taking the time to have a look.
February 4, 2016 at 9:57 am
I'm not asking for a data set with thousands of rows. I'm just asking for something that can replicate the infinite loop problem and we can fix the issue.
I'm not sure about the logic you're following to build your strings.
February 4, 2016 at 1:33 pm
Hi,
The loop does not begin until the query has covered 500+ records as I said it is a very large recursive query and I would have to adjust to publish on the web. Thank you for taking a look anyway.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply