July 30, 2012 at 1:28 am
I have a CTE that is in a loop. I need to limit the recursive query so that data that has already been selected is not selected again.
So if I a CustNo has already been selected in the recursion, it should not be selected again.
Here is my table:
/****** ******/
DROP TABLE [dbo].[T1]
GO
/****** ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T1](
[CustNo] [varchar](5) NULL,
[AppKey] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'111', N'222')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'111', N'228')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'112', N'228')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'222', N'232')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'228', N'230')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'228', N'222')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'229', N'231')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'230', N'222')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'231', N'222')
INSERT [dbo].[T1] ([CustNo], [AppKey]) VALUES (N'232', N'230')
From this table, I am interested in determining all the Appkeys that are related to CustNo. So when I search for CustNo = 111, I expect the following result set:
111
222
228
230
232
And here is my CTE Query:
DECLARE @p_CustNo varchar(3) = '111';
WITH tmpHH (CustNo, AppKey, Level)
AS
(
-- Anchor ....
SELECT e.CustNo, e.AppKey, 0 as Level
From [dbo].[T1] AS e
WHERE CustNo = @p_CustNo
UNION ALL
-- Recursive ...
SELECT e.CustNo, e.AppKey, Level + 1
FROM [dbo].[T1] AS e
INNER JOIN tmpHH as D
ON d.AppKey = e.CustNo
)
SELECT *
FROM tmpHH
--order by Level
However, this results in an infinite loop. To eliminate this loop, I need to limit the Recursive query to select only CustNo values that do not exist in "e".
But when I add this to my recursive query, I get a syntax error:
SELECT e.CustNo, e.AppKey, Level + 1
FROM [dbo].[T1] AS e
INNER JOIN tmpHH as D
ON d.AppKey = e.CustNo
WHERE not exists (select e.CustNo from e Inner Join d on d.AppKey = e.CustNo)
Invalid object name 'e'.
July 30, 2012 at 3:05 am
santlou (7/30/2012)
I have a CTE that is in a loop. I need to limit the recursive query so that data that has already been selected is not selected again....To eliminate this loop, I need to limit the Recursive query to select only CustNo values that do not exist in "e"....
'e' is your source table,
FROM [dbo].[T1] AS e
I think you mean the recursive part, alias 'd'.
Only the rows which were matched in the last recursion can be "seen" by the current recursion so this won't work. There are two obvious solutions to this - either fix the data so there are no feedback loops, or restrict the rCTE to a fixed number of levels and clean the result set.
WHERE not exists (select e.CustNo from e Inner Join d on d.AppKey = e.CustNo)
This part fails because you've forgotten to include the table names with the aliases.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply