Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Максим Чистяков
Member

Откуда:
Сообщений: 23
Добрый день.
Вопрос, который не даёт покоя: почему в Select нельзя передать в пользовательскую табличную функцию имена параметров, получаемые этим же SELECT из столбцы таблицы?
Столбцы таблицы Puppies:
IDNumberNameIDParent
1001 01Барбос1006
100202Мухтар1006
100303...1006
100404...1006
100505...1006
100600...NULL
100706...1001
100807...1002
100908...1003


Я хочу получить цепочку из Number для каждого ID на основании IDParent по иерархии прародителей, а также вычислить уровень от первого родителя. Для этого создаю функцию, которая возвращает табличное значение
+
create function dbo.somefunc(@currID float)
returns @tSomeFunc Table (Path varchar(30), Level int)
as
begin
	declare @vLevel int = 1;
	declare @result varchar(10) ='';
	declare @currSMCD varchar(10);
	declare @vIDPARENT int = (select IDPARENT from WORKCENTER where ID = @currID);

	while (@vIDPARENT is not null)
	begin
		set @currSMCD = (select SMNEMOCODE from WORKCENTER where ID = @currID);
		set @currID = @vIDPARENT;
		set @vIDPARENT = (select IDPARENT from WORKCENTER where ID = @currID); 
		if @result  =''
		begin
			set @result = @currSMCD
			continue
		end
		set @result = @currSMCD + '-' + @result;
		set @vLevel += 1;

	end;

	set @currSMCD = (select SMNEMOCODE from WORKCENTER where ID = @currID);
	if @result  =''
	set @result = @currSMCD
	else set @result = @currSMCD + '-' + @result;
	insert into @tSomeFunc select top 1 @result, @vLevel
	return;
end
go


Чтобы не изучать смысл функции, скажу, что она склеивает Number'ы и возвращает их в @result, вычисляет уровень счётчиком и возвращает его в vLevel.

Если сделать
select * from dbo.somefunc (1001)
она вернёт требующиеся значения

Однако, если я не пойму, как передать в неё ID из таблицы. Когда я пишу
SELECT dbo.somefunc(P.ID) from Puppies as P

получаю ответ Не удалось найти столбец "dbo", определяемую пользователем функцию или агрегатную функцию "dbo.somefunc". Также возможно, имя является неоднозначным.

Масла подливает то, что если приведённую выше функцию превратить в две, которые вместо TABLE они возвращали одна varchar , другая int, и соединить их в виде
select dbo.findLevel(P.ID) as Уровень, dbo.findPath(P.ID) as Путь from Puppies as P

то работать они будут:
+
LevelPath
200-01
300-01-06
200-02
300-02-07
200-03
300-03-08
200-04
200-05

?7 КАК ТАКОЕ МОЖЫД БЫДЬ

Спасибо.
27 мар 18, 12:52    [21289577]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36889
SELECT 
  F.*
from Puppies as P
cross /*outer*/ apply dbo.somefunc(P.ID) F
27 мар 18, 12:55    [21289596]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Максим Чистяков
?7 КАК ТАКОЕ МОЖЫД БЫДЬ

MSDN
27 мар 18, 13:00    [21289615]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Максим Чистяков
Member

Откуда:
Сообщений: 23
Ошибся с запросом создания функции, вот правильный код
+
create function dbo.somefunc(@currID float)
returns @tSomeFunc Table (Path varchar(30), Level int)
as
begin
	declare @firstID float = @currID;
	declare @vLevel int = 1;
	declare @result varchar(10) ='';
	declare @currSMCD varchar(10);
	declare @vIDPARENT int = (select IDPARENT from Puppies where ID = @currID);

	while (@vIDPARENT is not null)
	begin
		set @currSMCD = (select Number from Puppies where ID = @currID);
		set @currID = @vIDPARENT;
		set @vIDPARENT = (select IDPARENT from Puppies where ID = @currID); 
		if @result  =''
		begin
			set @result = @currSMCD
			continue
		end
		set @result = @currSMCD + '-' + @result;
		set @vLevel += 1;

	end;

	set @currSMCD = (select Number from Puppies where ID = @currID);
	if @result  =''
	set @result = @currSMCD
	else set @result = @currSMCD + '-' + @result;
	insert into @tSomeFunc select top 1 @result, @vLevel
	return;
end
go
27 мар 18, 13:03    [21289631]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Максим Чистяков,

Попробуй таким образом:
set @currSMCD = (select top 1 SMNEMOCODE from WORKCENTER where ID = @currID);
27 мар 18, 13:08    [21289657]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Максим Чистяков
Member

Откуда:
Сообщений: 23
Гавриленко Сергей Алексеевич, спасибо.
27 мар 18, 13:09    [21289661]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Максим Чистяков,

функция не нужна. CTE в самом оригинально применении
27 мар 18, 13:11    [21289670]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Kopelly,
А вообще - Смотри первый ответ...
27 мар 18, 13:14    [21289679]     Ответить | Цитировать Сообщить модератору
 Re: Не удаётся включить функцию, возвращающую таблицу, в список SELECT  [new]
Максим Чистяков
Member

Откуда:
Сообщений: 23
Большое спасибо всем, кто помог. Узнал кое-что полезное.
27 мар 18, 18:53    [21291127]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить