نمی توانم از لوپ CTE خارج شوم.

[رضا عربلو]

نمی توانم از لوپ CTE خارج شوم.
به قول فرنگی ها I’m new in CTE :(
برای اینکه مسئله را بهتر توضیح دهم یک جدول دارم با نام tDocuments با تقریباً 700 رکورد. به شکل


USE [BNPP]
GO
/****** Object: Table [dbo].[tDocuments] Script Date: 01/08/2008 15:48:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tDocuments](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DocumentCode] [nvarchar](255) NULL,
[ReferenceCode] [nvarchar](255) NULL,
CONSTRAINT [PK_tDocuments] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

که درآن فیلد DocumentCode کد مدرک و فیلد ReferenceCode نیز کد مدرکی است که مدرک فوق به آن ارجاع داده شده است.
حالا می خواهم تمام مدارکی که به نحوی (حتی با طی چند مرحله) به مدرک با کد ’21.BU.1.BA.ZK.RDR.001_1’ ارجاع داده شده اند و یا مدرک فوق به آنها ارجاع داده شده است را پیدا کنم.
کوئری زیر را نوشتم.


WITH CTE_GETALLRelatedDocuments (DocumentCode) AS
(
SELECT e.DocumentCode AS DocumentCode FROM tDocuments AS e WHERE e.ReferenceCode='21.BU.1.BA.ZK.RDR.001_1'
UNION ALL
SELECT e.ReferenceCode AS DocumentCode FROM tDocuments AS e WHERE e.DocumentCode='21.BU.1.BA.ZK.RDR.001_1'
UNION ALL
SELECT e.ReferenceCode AS DocumentCode FROM tDocuments AS e INNER JOIN CTE_GETALLRelatedDocuments AS cte ON e.DocumentCode = cte.DocumentCode
UNION ALL
SELECT e.DocumentCode AS DocumentCode FROM tDocuments AS e INNER JOIN CTE_GETALLRelatedDocuments AS cte ON e.ReferenceCode = cte.DocumentCode
)
SELECT DocumentCode
FROM CTE_GETALLRelatedDocuments
OPTION (MAXRECURSION 800)
GO

ولی متاسفانه نمی توانم از لوپ CTE خارج شوم.

[AminSobati]

رضا جان پیشنهاد میکنم 2 تا Recursive CTE داشته باشین. یکیش از سند به سمت بالا پیمایش کنه و دیگری به سمت پایین. نتیجه رو با هم union کنین:


with
cte_up as (...),
cte_down as (...)
select * from cte_up
union all
select * from cte_down

[رضا عربلو]

آقای ثباتی، بسیار سپاسگذارم. تست کردم، متاسفانه اونی که می خواستم نبود. در واقع کوئری فوق بخشی از نتیجه مورد نظر من را می دهد. برای اینکه بهتر مسئله را توضیح بدهم . مثال زیر را در نظر بگیرید.
برای مثال در سناریوی زیر
DocumentCode ReferenceCode
-------------------------------------
2<---------------------------1
3<---------------------------2
3<---------------------------4
برای نقطه آغازین 2 بایستی 1و 2و 3و 4 را بدست بیاورم، که 4 در جواب شما نیست.
این هم شکل کامل کوئری شما:


with
cte_up as(
SELECT e.DocumentCode AS DocumentCode FROM tDocuments AS e WHERE e.ReferenceCode='2'
UNION ALL
SELECT e.DocumentCode AS DocumentCode FROM tDocuments AS e INNERJOIN cte_up AS cte ON e.ReferenceCode = cte.DocumentCode
)
,
cte_down as(
SELECT e.ReferenceCode AS DocumentCode FROM tDocuments AS e WHERE e.DocumentCode='2'
UNION ALL
SELECT e.ReferenceCode AS DocumentCode FROM tDocuments AS e INNERJOIN cte_up AS cte ON e.DocumentCode = cte.DocumentCode
)
select*from cte_up
union all
select*from cte_down

[رضا عربلو]

اینطور که به نظر می رسد CTE هنوز برای Recursive شدن به اون شکلی که در برنامه نویسی سراغ ئاریم خیلی جا داشته باشد. کمبود وجود یک سری دستورات کنترلی و یا ساختار گرامری بسیار محدود کننده آن محسوس است.
برای مثال در دستور cte پیمایش یک درخت سلسه مراتبی اگر در یک جا فیلد A به فیلد B اشاره کند و همچنین در جایی دیگر فیلد B به فیلد A اشاره کند کوئری مان ناخواسته درون یک لوپ می افتد.
اینطور که من فهمیدم CTE Recusive تنها برای حل مسائل محدودی کاربرد دارد!.
اینطور نیست؟

[AminSobati]

شرطهای شما به نظر میرسه اشکالی داشته باشند. در CTE اول که به سمت بالا پیمایش میکنید، این رو قرار بدین:
cte.ReferenceCode =e.DocumentCode
و در دومی:
e.ReferenceCode =cte.DocumentCode

و نکته مهمتر اینکه در CTE دوم باز هم دارین از cte_up استفاده میکنین! در حالیکه در cte_down هستین

[رضا عربلو]

امین جان تصحیح شد ولی باز نتیجه مورد نظر من را نداد.


برای مثال در سناریوی زیر
DocumentCode ReferenceCode
-------------------------------------
2<---------------------------1
3<---------------------------2
3<---------------------------4
برای نقطه آغازین 2 بایستی 1و 2و 3و 4 را بدست بیاورم، که 4 در جواب شما نیست.

[AminSobati]

رضا جان نمیدونم مشکل از Query آب میخوره یا از دیتا! این رو من برای northwind.dbo.employees که ساختار درختی داره نوشتم و درست جواب میگیرم. نقطه آغازین من کارمند شما 5 هستش که یک سطح بالاتر و یک سطح در پایین تر از خودش داره:


with
up as
(select employeeid,lastname,reportsto from employees where employeeid=5
union all
select e.employeeid,e.lastname,e.reportsto from employees e join up on up.reportsto=e.employeeid),
down as
(select employeeid,lastname,reportsto from employees where employeeid=5
union all
select e.employeeid,e.lastname,e.reportsto from employees e join down on e.reportsto=down.employeeid)
select * from up
union all
select * from down

[رضا عربلو]

مشکل از دیتاست. در واقع دیتای من ساختار درختی نداره و این کار مشکل می کنه.
فکر می کنم باید روشی پیدا کنم تا نتایج قبلی را از دور خارج بکنم تا تو لوپ نیفتم.