не могу понять офр ошибку

Albatross

Дата: 16.01.2009 10:23:04

SELECT * FROM (
WITH T (department_id, department_parent_id, L, LX) AS
	(	SELECT department_id, department_parent_id, 0 L, department_id
		FROM DEPARTMENT  (NOLOCK) WHERE department_parent_id = 1 
			UNION ALL
		SELECT a.department_id, a.department_parent_id, 1+L
		, CASE L WHEN 1 THEN a.department_parent_id ELSE LX END
		FROM DEPARTMENT A (NOLOCK) JOIN T ON A.department_parent_id=T.department_id
	)

	SELECT TOP 100 PERCENT KOL.department_id, KOL.department_parent_id
	FROM T KOL 
	JOIN DEPARTMENT A ON KOL.department_id= A.department_id
	ORDER BY L ASC 

)P

MS SQL 2005

Пишет
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

Собственно, хочу выражение выше ипользовать как подзапрос, а условие WHERE department_parent_id = 1
переписать на условие с полем надзапроса (в итоге - таблица вида отдел->все потомки отдела)

pkarklin

Дата: 16.01.2009 10:24:27

автор

не могу понять офр ошибку

А Вы синтаксис использования CTE не хотите на примерах BOL посмотреть, прежде чем отсебятиной заниматься?!

iap

Дата: 16.01.2009 12:20:26

Albatross

SELECT * FROM (
WITH T (department_id, department_parent_id, L, LX) AS
	(	SELECT department_id, department_parent_id, 0 L, department_id
		FROM DEPARTMENT  (NOLOCK) WHERE department_parent_id = 1 
			UNION ALL
		SELECT a.department_id, a.department_parent_id, 1+L
		, CASE L WHEN 1 THEN a.department_parent_id ELSE LX END
		FROM DEPARTMENT A (NOLOCK) JOIN T ON A.department_parent_id=T.department_id
	)

	SELECT TOP 100 PERCENT KOL.department_id, KOL.department_parent_id
	FROM T KOL 
	JOIN DEPARTMENT A ON KOL.department_id= A.department_id
	ORDER BY L ASC 

)P

WITH T (department_id, department_parent_id, L, LX) AS
	(	SELECT department_id, department_parent_id, 0 L, department_id
		FROM DEPARTMENT  (NOLOCK) WHERE department_parent_id = 1 
			UNION ALL
		SELECT a.department_id, a.department_parent_id, 1+L
		, CASE L WHEN 1 THEN a.department_parent_id ELSE LX END
		FROM DEPARTMENT A (NOLOCK) JOIN T ON A.department_parent_id=T.department_id
	),
     P AS
	(
	SELECT KOL.L, KOL.department_id, KOL.department_parent_id
	FROM T KOL 
	JOIN DEPARTMENT A ON KOL.department_id= A.department_id
	)
SELECT * FROM P ORDER BY L;