Is Cursor Included in Yukon/SQL 2005. or any alternet solution for that?Yes 2005 still supports cursors. There is already an alternative solution in
earlier versions: set-based SQL code. If you tell us what you want to do
then we might be able to give you a specific solution.
David Portas
SQL Server MVP
--|||Thanks for your reply......
Just i want to know MS has included cursor in Yukon? if yes then any
new feature in cursor? and if no what is the alernet solution for that
thats what i wanted to know....
HItendra|||I'm not aware of any new features of cursors in 2005 but then I haven't
looked for them. If it's important to you then the best place for that
question is the SQL Server 2005 beta newsgroups.
Cursors are almost never necessary in SQL Server and they are seldom a good
idea. Since you haven't told us what you want to do I can't tell you what th
e
alternatives are. "Cursor" is not a specification of a problem - it's just
one type of solution. The better solutions are usually set-based. Set-based
statements operate on the whole set of rows (like SELECT/UPDATE/DELETE/INSER
T
statements) instead of one row at a time (like a cursor). Most of the time i
t
pays to find a set-based solution rather than a cursor because set-based cod
e
is generally easier to develop, test, support and maintain as well as being
much more efficient than most cursor solutions.
David Portas
SQL Server MVP
--
"hitendra15@.gmail.com" wrote:
> Thanks for your reply......
> Just i want to know MS has included cursor in Yukon? if yes then any
> new feature in cursor? and if no what is the alernet solution for that
> thats what i wanted to know....
> --
> HItendra
>|||(hitendra15@.gmail.com) writes:
> Thanks for your reply......
> Just i want to know MS has included cursor in Yukon? if yes then any
> new feature in cursor?
There are no new features with cursors, as far as I know. The version
with the feature-craze on cursors was SQL 7, and I still haven't understood
all of that. Keep your cursors INSENSITIVE, STATIC or FAST_FORWARD, and
stay away from the default keyset cursors.
> and if no what is the alernet solution for that thats what i wanted to
> know....
Rather cursors are the alternate solution when you simply cannot find a
set-based solution. That happens, but it is not that frequent. Here is a
quick example of a set-based solution. Say that you have an employee
table, and you are to update the salaries. Generally there is a two
percent increase, but staff that earns less than 15000 SEK/month gets
300 SEK extra, and all women receive an extra 0.5% raise. This can be
done in one UPDATE:
UPDATE employees
SET salary = 1.02 * salary +
CASE WHEN salary < 15000 THEN 300 ELSE 0 END +
CASE sex WHEN 'K' THEN 1.005 * salary ELSE 0 END
Ah, when I think of it, if you really like iterattion, yes there is now
a new way to do this in SQL 2005, as you now can write stored procedures
in a .Net language such as C#. This gives new oppurtunities to implement
good solutions in SQL Server - and also a lot of new oppurtunities to
implement really poor solutions if implemented improperly.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> all women receive an extra 0.5% raise. This can be
> done in one UPDATE:
Fine example, but is it legal in any jurisdiction you know of?
As you've mentioned, iteration is a feature of cursors and we do have new
non-cursor features in SQL2005 that are relevant to those types of problem.
The CROSS APPLY operator means we no longer need to use cursors for expandin
g
an adjacency list and similar tasks. Also, the ROW_NUMBER function is a
convenient way to implement sequences over data. So there should be even les
s
reason to use cursors in SQL2005.
David Portas
SQL Server MVP
--|||Yes, cursors are included in SQL 2005. I do not know of any new features of
cursors. They are not the preferred way of doing work (since SQL 7.0), so I
do not use them unless I absolutely have to. This follows MS suggestions and
may be the reason cursors do not change much.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"hitendra15@.gmail.com" wrote:
> Is Cursor Included in Yukon/SQL 2005. or any alternet solution for that?
>|||Yes, they have cursors, but if you had been writing good SQL, you would
not be using them more than a few times in 10-20 years of production
coding anyway.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Fine example, but is it legal in any jurisdiction you know of?
Interesting question. If an employer over here would do this, but give the
extra raise to men, there would be one h*ll of a noise. But for women?
My guess, is that they would get away with it. Even more so, if this was
the result of negotiations with the union. Oh, well, wrong newsgroup
I think.
> As you've mentioned, iteration is a feature of cursors and we do have
> new non-cursor features in SQL2005 that are relevant to those types of
> problem. The CROSS APPLY operator means we no longer need to use cursors
> for expanding an adjacency list and similar tasks. Also, the ROW_NUMBER
> function is a convenient way to implement sequences over data. So there
> should be even less reason to use cursors in SQL2005.
Great examples!
Of what I can think of, left is the case when you have a stored procedure
which you want to reuse, and you don't find it worth the effort to
rewrite the stored procedure for handle an input set. And then of course
the few exceptional cases where cursors actually are faster.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thank you guys i understood that there is a cursor but its not improved
no new features to it,
so it seems me that Procedures, UDT, Functions, triggers etc are Part
of SQL CLR[We can make], cursor is not....am i correct?
Hitendra
Showing posts with label included. Show all posts
Showing posts with label included. Show all posts
Sunday, March 25, 2012
Sunday, February 19, 2012
CTE - determine closest item
I have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve:
(1)
I want to create a NEW stored procedure that takes a WhoID and an AssocID.
This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be
6 - 8
8 - 2
2 - 3
3 - 1
(2)
I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go
create proc dbo.procCTE_Assoc
@.WhoID bigint
AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @.WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)
SELECT *
FROM GroupsCTE
ORDER BY Sort
RETURN (0)
GO
exec procCTE_Assoc 6
To Find the Shortest Path,
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
declare @.findfrom as int
declare @.findto as int
set @.findfrom = 1
set @.findto = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
where
[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
and p.[from] <> @.findto
)
, pathordered
as
(
select * , min(level) over() minpathlevel from cte where path like '%\(' + cast(@.findto as varchar) + ')'
)
--select [path],[nodepath] from pathordered where level=minpathlevel
select p.[from], p.[to] from #paths p join pathordered po
onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
and po.level=po.minpathlevel
drop table #paths
|||
Wow. That is amazing. Thanks for your help. I wish I knew how it worked. Hopefully I will be able to study your code and try to understand what it does. I modified the code so that it would work against the existing table and I created a proc out of this code. I created another proc where I loaded the results of your query/cte into a temp table and I loaded the results from the cte that I posted into a temp table and I joined the two results (so that I could get the order correct -- Lvl).
Now, does anyone have any idea how to show all nodes / branches where a given item is related to another item (part 2 of my original post)?
--
Keith
|||For your Question 2
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
--where
--[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
--select distinct p.[from], p.[to],po.Path from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'order by path
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
drop table #paths
|||
With all paths (not only From & To with orignal path),
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 6
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
,requiredpaths
as
(
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
)
,CTE2
as
(
select *
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
from requiredpaths Where [from] not in (Select [to] from requiredpaths)
Union All
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
from
requiredpaths p
join cte2 c
on p.[from] = c.[to]
)
Select * from cte2 order By path
drop table #paths
|||
How do I prevent the following error?
Msg 530, Level 16, State 1, Line 12
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I receive that error when I execute the CTE against this set of data:
/*
set @.findfrom = 7
set @.findto = 21
*/
INSERT INTO #paths ([from],[to])VALUES(1,4)
INSERT INTO #paths ([from],[to])VALUES(2,NULL)
INSERT INTO #paths ([from],[to])VALUES(2,1)
INSERT INTO #paths ([from],[to])VALUES(2,3)
INSERT INTO #paths ([from],[to])VALUES(6,7)
INSERT INTO #paths ([from],[to])VALUES(6,8)
INSERT INTO #paths ([from],[to])VALUES(7,NULL)
INSERT INTO #paths ([from],[to])VALUES(7,20)
INSERT INTO #paths ([from],[to])VALUES(7,22)
INSERT INTO #paths ([from],[to])VALUES(7,26)
INSERT INTO #paths ([from],[to])VALUES(7,28)
INSERT INTO #paths ([from],[to])VALUES(7,35)
INSERT INTO #paths ([from],[to])VALUES(7,38)
INSERT INTO #paths ([from],[to])VALUES(7,44)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,49)
INSERT INTO #paths ([from],[to])VALUES(7,50)
INSERT INTO #paths ([from],[to])VALUES(7,51)
INSERT INTO #paths ([from],[to])VALUES(7,52)
INSERT INTO #paths ([from],[to])VALUES(7,53)
INSERT INTO #paths ([from],[to])VALUES(7,54)
INSERT INTO #paths ([from],[to])VALUES(7,55)
INSERT INTO #paths ([from],[to])VALUES(7,56)
INSERT INTO #paths ([from],[to])VALUES(8,2)
INSERT INTO #paths ([from],[to])VALUES(14,8)
INSERT INTO #paths ([from],[to])VALUES(20,21)
INSERT INTO #paths ([from],[to])VALUES(22,23)
INSERT INTO #paths ([from],[to])VALUES(22,31)
INSERT INTO #paths ([from],[to])VALUES(23,25)
INSERT INTO #paths ([from],[to])VALUES(26,27)
INSERT INTO #paths ([from],[to])VALUES(28,29)
INSERT INTO #paths ([from],[to])VALUES(29,30)
INSERT INTO #paths ([from],[to])VALUES(31,32)
INSERT INTO #paths ([from],[to])VALUES(32,34)
INSERT INTO #paths ([from],[to])VALUES(35,36)
INSERT INTO #paths ([from],[to])VALUES(36,37)
INSERT INTO #paths ([from],[to])VALUES(38,39)
INSERT INTO #paths ([from],[to])VALUES(39,0)
INSERT INTO #paths ([from],[to])VALUES(39,40)
INSERT INTO #paths ([from],[to])VALUES(39,41)
INSERT INTO #paths ([from],[to])VALUES(41,NULL)
INSERT INTO #paths ([from],[to])VALUES(41,42)
INSERT INTO #paths ([from],[to])VALUES(42,41)
CTE - determine closest item
I have included the create table statements, sample data and a stored procedure.
I have a stored procedure that walks the associations so that I can assemble all the nodes of the tree. I think this is working properly.
I have two requests/needs/problems that I need to solve:
(1)
I want to create a NEW stored procedure that takes a WhoID and an AssocID.
This new stored procedure would show the shortest or closest path between WhoID and AssocID.
For example, if I pass in 6,1 to this new proc I want the stored procedure to return the hierarchy with the closest path between them. Based on the sample data below, the path would be
6 - 8
8 - 2
2 - 3
3 - 1
(2)
I might also need a proc that accepts two inputs (again, lets use 6,1) but would show all paths / branches of the tree where person 1 exists. Based on the stored procedure procCTE_Assoc 6, this solution would exclude the "fred and jeff" node but would include the other two nodes because person 1 is within the other two nodes.
Please provide your suggestions, comments, or solutions!
CREATE TABLE dbo.Who(
WhoID bigint IDENTITY(1,1) NOT NULL,
FName varchar(50) NOT NULL
)
GO
CREATE TABLE dbo.Assoc(
WhoID bigint NOT NULL,
AssocID bigint NOT NULL
) ON PRIMARY
SET IDENTITY_INSERT dbo.Who ON
GO
INSERT INTO Who (WhoID,FName) VALUES (1,'user1')
INSERT INTO Who (WhoID,FName) VALUES (2,'user2')
INSERT INTO Who (WhoID,FName) VALUES (3,'admin1')
INSERT INTO Who (WhoID,FName) VALUES (4,'admin2')
INSERT INTO Who (WhoID,FName) VALUES (5,'admin3')
INSERT INTO Who (WhoID,FName) VALUES (6,'Keith')
INSERT INTO Who (WhoID,FName) VALUES (7,'fred')
INSERT INTO Who (WhoID,FName) VALUES (8,'Joe')
INSERT INTO Who (WhoID,FName) VALUES (9,'Jack')
INSERT INTO Who (WhoID,FName) VALUES (10,'Doug')
INSERT INTO Who (WhoID,FName) VALUES (11,'Mark')
INSERT INTO Who (WhoID,FName) VALUES (12,'Al')
INSERT INTO Who (WhoID,FName) VALUES (13,'Jeff')
INSERT INTO Who (WhoID,FName) VALUES (14,'Brandon')
INSERT INTO Who (WhoID,FName) VALUES (15,'Ashley')
INSERT INTO Who (WhoID,FName) VALUES (16,'Lisa')
INSERT INTO Who (WhoID,FName) VALUES (17,'Julie')
INSERT INTO Who (WhoID,FName) VALUES (18,'Brian')
go
SET IDENTITY_INSERT dbo.Who OFF
GO
INSERT INTO Assoc (WhoID,AssocID) VALUES (1,4)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,1)
INSERT INTO Assoc (WhoID,AssocID) VALUES (2,3)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,7)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (7,13)
INSERT INTO Assoc (WhoID,AssocID) VALUES (8,2)
INSERT INTO Assoc (WhoID,AssocID) VALUES (14,8)
INSERT INTO Assoc (WhoID,AssocID) VALUES (6,15)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,18)
INSERT INTO Assoc (WhoID,AssocID) VALUES (15,17)
INSERT INTO Assoc (WhoID,AssocID) VALUES (17,5)
INSERT INTO Assoc (WhoID,AssocID) VALUES (5,1)
go
create proc dbo.procCTE_Assoc
@.WhoID bigint
AS
WITH GroupsCTE (WhoID, AssocID, FName, FNameAssoc, Lvl, Sort, GroupWithIndent)
AS
(
-- Anchor Who definition
SELECT M.WhoID, MA.AssocID, M.FName, AssocWho.FName, 0 AS Lvl,
CONVERT(varchar(2000),AssocWho.FName) AS Sort,
CONVERT(varchar(255), AssocWho.FName)
FROM Who M
INNER JOIN Assoc MA ON M.WhoID = MA.WhoID
INNER JOIN Who AssocWho ON AssocWho.WhoID = MA.AssocID
WHERE M.WhoID = @.WhoID
UNION ALL
-- Recursive Who definition
SELECT g1.WhoID, MA1.AssocID, g1.FName, g2.FName, Lvl + 1,
CONVERT(varchar(2000),RTRIM(Sort) + '|' + g2.FName),
CONVERT(varchar(255), REPLICATE ('| ' , Lvl+1) + g2.FName)
FROM Who g1
INNER JOIN Assoc MA1 ON g1.WhoID = MA1.WhoID
INNER JOIN Who g2 ON g2.WhoID = MA1.AssocID
INNER JOIN GroupsCTE CTE
ON MA1.WhoID = CTE.AssocID
)
SELECT *
FROM GroupsCTE
ORDER BY Sort
RETURN (0)
GO
exec procCTE_Assoc 6
To Find the Shortest Path,
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
declare @.findfrom as int
declare @.findto as int
set @.findfrom = 1
set @.findto = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
where
[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
and p.[from] <> @.findto
)
, pathordered
as
(
select * , min(level) over() minpathlevel from cte where path like '%\(' + cast(@.findto as varchar) + ')'
)
--select [path],[nodepath] from pathordered where level=minpathlevel
select p.[from], p.[to] from #paths p join pathordered po
onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
and po.level=po.minpathlevel
drop table #paths
|||
Wow. That is amazing. Thanks for your help. I wish I knew how it worked. Hopefully I will be able to study your code and try to understand what it does. I modified the code so that it would work against the existing table and I created a proc out of this code. I created another proc where I loaded the results of your query/cte into a temp table and I loaded the results from the cte that I posted into a temp table and I joined the two results (so that I could get the order correct -- Lvl).
Now, does anyone have any idea how to show all nodes / branches where a given item is related to another item (part 2 of my original post)?
--
Keith
|||For your Question 2
Code Snippet
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 9
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
--where
--[from] = @.findfrom
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
--select distinct p.[from], p.[to],po.Path from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'order by path
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
drop table #paths
|||
With all paths (not only From & To with orignal path),
create table #paths (
[from] int ,
[to] int
);
insert into #paths values('1','2');
insert into #paths values('2','3');
insert into #paths values('2','5');
insert into #paths values('3','5');
insert into #paths values('5','6');
insert into #paths values('6','7');
insert into #paths values('7','9');
insert into #paths values('5','9');
insert into #paths values('2','10');
declare @.find as int
set @.find = 6
;with cte
as
(
select
*
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
, cast('(' + cast([from] as varchar) +'-' + cast([to] as varchar) + ')' as varchar(max)) as [nodepath]
from
#paths
union all
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
, cast([nodepath]+ '\('+ cast(p.[from] as varchar) + '-' + cast(p.[to] as varchar) + ')' as varchar(max))
from
#paths p
join cte c
on p.[from] = c.[to]
)
,Finder
as
(
Select * from CTE Where path like '%(' + cast(@.find as varchar) + ')%'
)
,requiredpaths
as
(
select distinct p.[from], p.[to] from #paths p join Finder po onpo.[nodepath] like '%(' + cast(p.[from] as varchar) +'-' + cast(p.[to] as varchar) + ')%'
)
,CTE2
as
(
select *
, cast(0 as int) [level]
, cast('(' + cast([from] as varchar) +')\(' + cast([to] as varchar) + ')' as varchar(max)) as [path]
from requiredpaths Where [from] not in (Select [to] from requiredpaths)
Union All
select
p.*
, [level] + 1
, cast([path]+ '\(' + cast(p.[to] as varchar) + ')' as varchar(max))
from
requiredpaths p
join cte2 c
on p.[from] = c.[to]
)
Select * from cte2 order By path
drop table #paths
|||
How do I prevent the following error?
Msg 530, Level 16, State 1, Line 12
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I receive that error when I execute the CTE against this set of data:
/*
set @.findfrom = 7
set @.findto = 21
*/
INSERT INTO #paths ([from],[to])VALUES(1,4)
INSERT INTO #paths ([from],[to])VALUES(2,NULL)
INSERT INTO #paths ([from],[to])VALUES(2,1)
INSERT INTO #paths ([from],[to])VALUES(2,3)
INSERT INTO #paths ([from],[to])VALUES(6,7)
INSERT INTO #paths ([from],[to])VALUES(6,8)
INSERT INTO #paths ([from],[to])VALUES(7,NULL)
INSERT INTO #paths ([from],[to])VALUES(7,20)
INSERT INTO #paths ([from],[to])VALUES(7,22)
INSERT INTO #paths ([from],[to])VALUES(7,26)
INSERT INTO #paths ([from],[to])VALUES(7,28)
INSERT INTO #paths ([from],[to])VALUES(7,35)
INSERT INTO #paths ([from],[to])VALUES(7,38)
INSERT INTO #paths ([from],[to])VALUES(7,44)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,48)
INSERT INTO #paths ([from],[to])VALUES(7,49)
INSERT INTO #paths ([from],[to])VALUES(7,50)
INSERT INTO #paths ([from],[to])VALUES(7,51)
INSERT INTO #paths ([from],[to])VALUES(7,52)
INSERT INTO #paths ([from],[to])VALUES(7,53)
INSERT INTO #paths ([from],[to])VALUES(7,54)
INSERT INTO #paths ([from],[to])VALUES(7,55)
INSERT INTO #paths ([from],[to])VALUES(7,56)
INSERT INTO #paths ([from],[to])VALUES(8,2)
INSERT INTO #paths ([from],[to])VALUES(14,8)
INSERT INTO #paths ([from],[to])VALUES(20,21)
INSERT INTO #paths ([from],[to])VALUES(22,23)
INSERT INTO #paths ([from],[to])VALUES(22,31)
INSERT INTO #paths ([from],[to])VALUES(23,25)
INSERT INTO #paths ([from],[to])VALUES(26,27)
INSERT INTO #paths ([from],[to])VALUES(28,29)
INSERT INTO #paths ([from],[to])VALUES(29,30)
INSERT INTO #paths ([from],[to])VALUES(31,32)
INSERT INTO #paths ([from],[to])VALUES(32,34)
INSERT INTO #paths ([from],[to])VALUES(35,36)
INSERT INTO #paths ([from],[to])VALUES(36,37)
INSERT INTO #paths ([from],[to])VALUES(38,39)
INSERT INTO #paths ([from],[to])VALUES(39,0)
INSERT INTO #paths ([from],[to])VALUES(39,40)
INSERT INTO #paths ([from],[to])VALUES(39,41)
INSERT INTO #paths ([from],[to])VALUES(41,NULL)
INSERT INTO #paths ([from],[to])VALUES(41,42)
INSERT INTO #paths ([from],[to])VALUES(42,41)
Subscribe to:
Posts (Atom)