I am planning to develop a web based client for AS 2005 cubes. I am looking for some sample code/apps for jump start. I you know one please can you let me know the URL. I googled but all I found some articles
Thank you - Ashok
The CellSetGrid is open source ASP.NET based OLAP browser: http://www.sqlserveranalysisservices.com/cellsetgrid/CellSetGridIntro.htm
Hi,
I am started working with SAS2005 recently. Using Adventure works sample database.
When I login to the Management Studio as administrator and browse the cube, the browser filters all the null value columns and rows.
Steps:
1. Add Product.Product Categories to filter area and select only Bikes
2. Add Date.Calendar.Date Calendar year to filter area and select year 2005
3. Add Product.Product to Row area
4. Add Measures.Internet Sales.Internet Sales Amount in the Total or detail area
5. Add [Date].[Month of Year] in the column area.
When I logged in as administrator or the user with the role Full control (administrator) and all the nessasary permission set on all the objects. The cube browser displays the data where only it has values. If the entire row or column are null then the row and columns are not displayed.
When I do the exactly same with the user has a role instead of full control using just read definition option (I do not want the user to get full permission, like to make a role as read only) , the browse cube shows all the rows and columns, even the row and columns where all the values are null.
The empty cells are displayed as #N/A.
Is it possible to make the browser behave for the read only user as exactly like administrator user.
Any suggestions and recommendations are great.
Thanks
I am stuck with this problem. Please somebody help...tbl Projects{
id (int),
Project (xml)
}
sample data within the project column is
id = 100
Project =
'<Projects>
<SubProjects>
<id>150</id>
<id>160</id>
<id>170</id>
</SubProjects>
</Projects>'
basically i'm trying to set up a recursive query to get all sub projects for a given project.. this is PART of a bigger query ..
WITH MySubProjects(SubProjects)
AS
(
SELECT TblProjects.*, P.SubProjectIDs.value('(.)[1]','int') AS SubProjects
FROM RB_Projects TblProjects
CROSS APPLY Project.nodes('/Projects/SubProjects/id') AS P(SubProjectIDs)
where id = 100
)
but i keep getting the following error
Incorrect syntax near ')'.
does CTE's not like xquery functions within it's expression?n/m
guess i gotta use the CTE after definine it...
"A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
statement that
references some or all the CTE columns."
http://msdn2.microsoft.com/en-us/library/ms175972.aspx
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)
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)
