نحوه استفاده از CTE

[alireza1384]

با سلام ؛
من كد زير را در Query Analyser اجرا مي كنم ولي با پيغام خطا مواجه مي شوم :
WITH Managers AS
(
--initialization
SELECT EmployeeID, LastName, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
--recursive execution
SELECT e.employeeID,e.LastName, e.ReportsTo
FROM Employees e INNER JOIN Managers m
ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers

پيغام خطا :
Incorrect syntax near the keyword 'with'.
مشكل از كجاست ؟

[حمیدرضاصادقیان]

سلام. این کد درسته احتمالا شما دارید روی نسخه 2000 اونو اجرا میکنید. دستورات CTE مخصوص 2005 به بالاست و در 2000 وجود ندارند.
موفق باشید

[MOJTABAATEFEH]

دستورات CTE برای چکاری استفاده میشن و استفاده از اونها چه فایده ای نسبت به استفاده نکردن از اونها داره؟

با تشکر

[m_omrani]

CTE به معنی Common Table Expression است که توسط آن می توانید بر روی یک نتیجه موقت که با WITH مشخص می کنید، پرس و جوی دیگری را اجرا کنید (مثلاً UPDATE، DELETE، SELECT) و از SQL 2005 به بعد معرفی شد. دقت کنید که CTE لزوماً در حالت Recursion استفاده نمی شه و در حالت غیر بازگشت هم می تونید استفاده کنید.

در حالت Recursion یا بازگشت که برای جداول پدر-فرزندی (جدولی که کلید خارجی به خودش داره) که برای ایجاد درختواره ها استفاده می شن، ابتدا در یک پرس و جو (که در اسکریپت شما با قسمت initialization مشخص شده) باید رکورد ریشه رو مشخص کنید. معمولاً رکوردیه که کلید خارجی اش که پدر رکورد رو مشخص می کنه، NULL هست. بعد این رو UNION ALL می کنید با رکوردهای دیگه ای که از JOIN جدول مورد نظر با CTE ایجاد شده به دست میاد. (قسمتی که در اسکریپت شما با recursion مشخص شده).

چیزی که رخ می ده اینه که در اولین تکرار داخل WITH در قسمت initialization فقط رکورد ریشه و در قسمت recursion رکوردهایی قرار میگیره که ReportsTo اونها برابر EmployeeID به دست اومده از WITH باشه (که چون در اولین تکرار فقط رکورد ریشه داخل WITH قرار داره، رکوردهایی که به مجموعه نتیجه اضافه می شه که پدر اونها رکورد ریشه است. برای این که بهتر متوجه بشوید در پرس و جوی شما یعنی در اولین تکرار کارمندانی مشخص می شوند که مستقیماً به خود رئیس گزارش می دهند. رئیس هم کارمندیه که به کسی گزارش نمی ده، یعنی رکورد ریشه. سپس در تکرار بعدی کارمندانی مشخص می شن که به کارمندانی گزارش می دهند که اکنون در نتیجه ایجاد شده موقت قرار دارند. یعنی کارمندان رده دوم گزارش دهنده). این سیر اونقدر ادامه پیدا می کنه که دیگه رکوردی باقی نمونه. بعد می تونید با نتیجه ایجاد شده هر کاری که بخواهید بکنید. مثلاً روی اون SELECT بزنید. یعنی کاری که الان کرده اید.

نکته ای که بسیار باید بهش دقت کنید این که در رکوردهای جدول شما حلقه وجود نداشته باشه. یعنی رکوردی که پدرش یکی دیگه باشه و وقتی پیگیری می کنی می بینی آخرش به خودش می رسه. این کار باعث می شه که recursion هیچ وقت متوقف نشه.

. آخرین نکته این که اگه قبل از دستور WITH دستور دیگه ای نوشتید، دقت کنید اونو به سمی کالن ختم کرده باشید. مثلاً این طوری:

set no count on;

with ...