Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Create function(array like "in", dt)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
Кот Матроскин
Судя по всему я понял ТС-а - такую же штуку делает SSRS. Там есть параметры, которые могут принимать несколько значений, и в запросе так и пишешь
Нужно от автора всё таки больше конкретики.
И очевидно же, что задача нерешаемая, если именно в такой постановке.
22 июл 15, 18:00    [17922722]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
alexeyvg
Кот Матроскин
Судя по всему я понял ТС-а - такую же штуку делает SSRS. Там есть параметры, которые могут принимать несколько значений, и в запросе так и пишешь
Нужно от автора всё таки больше конкретики.
И очевидно же, что задача нерешаемая, если именно в такой постановке.


Почему - она вполне решаемая 17922555.
22 июл 15, 18:07    [17922746]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
alexeyvg,

Здравствуйте, все. Новый рабочий денЁк начался, а задача всё-таки осталась!
Позвольте я более конкретно опишу задачу!
Имеется функция табличного вида:
CREATE FUNCTION [dbo].[AllCreditorsForBUnit](@BUnit int, @curDate datetime)
RETURNS 
	@CREDITORS TABLE (  [KEY] int
	                 , [NAME] nvarchar(MAX)
					 , [ORD] int
					 , [NAME_EN_US] nvarchar(MAX)
					)
AS
begin

	with 
		P289020V0 as (select * from T289020V0 p)
		, pp as 
		(
		-- кредиторы для БЕ
		select p.[KEY]
		  from P289020V0 p
		  JOIN T289064 l on l.CREDITOR = p.[KEY]
		  JOIN ParentsAndChildsBU(@BUnit, @curDate) b ON b.[key] = l.BUSINESS_UNIT
		)
		-- кредиторы для БЕ с потомками + ('ROOT', 'ND')
		, ph as                                                                          
			(
			SELECT x.[KEY],[NAME], [ORD], [NAME_EN_US]
			FROM P289020V0 x                                                                                                                            
			) 
		-- кредиторы для БЕ с потомками + ('ROOT', 'ND')
--		, ppf as

		insert @CREDITORS
		select DISTINCT p.[KEY], p.NAME, p.ORD, p.NAME_EN_US
		
		 --p.[KEY], p.name, p.PARENT_KEY, p.ord, p.INDATE, p.OUTDATE, p.VERSION, p.START_DATE_PLAN, p.END_DATE_PLAN, p.EXTERNAL_KEY, ppf.ord as main_ord
		  from P289020V0 p 
		  JOIN pp ON (pp.[KEY] = p.[KEY])

	return
end


И имеется соответствующая платформа BI, где имеется возможность задавать входные параметры в виде единичной и множественной отметки! Это как раз и есть та самая @BUnit.
Путём экспериментов было выявлено что при единичной отметке в переменной передаётся только одно значение, например "1",
а при множественной отметки передаётся множество типа "1,2,3,4,5"

Как видно из функции, то при множественной отметке отработка функции табл. вида выдаст ошибку!

Так вот и необходимо как-то обработать эту самую функцию ТАК чтобы она могла принимать на входе, и "1", и "1,2,3,4,5" тоже.

sql server - 12-ый
23 июл 15, 10:44    [17924628]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
т.е. вам на sql server сервер прилетает вот это

select * from d_func(1, '2015-07-20')
или 
select * from d_func(1, 2, 3, 4, 5, 6, '2015-07-20')
или 
select * from d_func(1, 2, 3, '2015-07-20')


и вы хотите чтобы работало в любом случае?
Так задача не решаема, но вот если сделать первый параметр строкой, и передавать туда через , значения то их можно распарсить внутри функции.
ВОпрос остается как поставить ковычки

чтобы вот такой код
select * from d_func(1, 2, 3, 4, 5, 6, '2015-07-20')

стал таким
select * from d_func('1, 2, 3, 4, 5, 6', '2015-07-20')


просто меняя функцию на сервере(ее параметры и их тип) заставить ваше приложение изменить способ формирования кода вряд-ли удастся.

т.е. полюбому придется лезть в приложение .
23 июл 15, 11:24    [17924836]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
Jaffar,
Не поверишь - задача решаема и имеются аж 2 варианта, и я их решил.

Вариант 1:
-- Объявляем временную таблицу под массив Бизнес Единиц из передаваемой переменной @BUnit
IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = OBJECT_ID(N'tempdb..#BU_TABLE')) DROP TABLE #BU_TABLE;
IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = OBJECT_ID(N'tempdb..#CR_TABLE')) DROP TABLE #CR_TABLE;

CREATE TABLE #BU_TABLE ([KEY] int);
CREATE TABLE #CR_TABLE ([KEY] int,
						 [NAME] varchar(max),
						 [ORD] int,
						 [NAME_EN_US] varchar(max)
						);
DECLARE @KEY INT;

-- вставляем во временную таблицу все @BUnit в виде таблицы
insert into #BU_TABLE ([KEY])
select [KEY] from БИЗНЕС_ЕДИНИЦЫ where [KEY] in (@BUnit)

-- делаем курсор по временной таблице
/*начитали курсор*/
DECLARE cr_cursor CURSOR FOR  
SELECT [KEY] 
FROM #BU_TABLE 

OPEN cr_cursor   
FETCH NEXT FROM cr_cursor INTO @KEY  

WHILE @@FETCH_STATUS = 0   
BEGIN   
       insert into #CR_TABLE (
							  [KEY],
							  [NAME],
							  [ORD],
							  [NAME_EN_US]
							  )
		select  [KEY],
				[NAME],
				[ORD],
				[NAME_EN_US] 
		from AllCreditorsForBUnit(@KEY,GETDATE()) /*Юзаем функцию что выше описывал*/

       FETCH NEXT FROM cr_cursor INTO @KEY   
END   

CLOSE cr_cursor   
DEALLOCATE cr_cursor

select distinct
		[KEY],
		[NAME],
		[ORD],
		[NAME_EN_US]
from #CR_TABLE



Ну и Вариант 2:
IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = OBJECT_ID(N'tempdb..#BU_PARENT')) DROP TABLE #BU_PARENT;

CREATE TABLE #BU_PARENT ([KEY] INT NOT NULL);

insert into #BU_PARENT ([KEY])
select [KEY] from БИЗНЕС_ЕДИНИЦЫ  where [KEY] in (@BUnits) /*заменить на @BUnit*/


IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = OBJECT_ID(N'tempdb..#BU_CHILD')) DROP TABLE #BU_CHILD;
WITH REC ([KEY], [PARENT_KEY], [LEVEL]) 
AS (  
       SELECT БИЗНЕС_ЕДИНИЦЫ .[KEY], БИЗНЕС_ЕДИНИЦЫ .[CONS_CONTUR_LNK] AS [PARENT_KEY], 0 AS [LEVEL] 
             FROM #BU_PARENT AS P
                    JOIN БИЗНЕС_ЕДИНИЦЫ ON БИЗНЕС_ЕДИНИЦЫ .[KEY] = P.[KEY]  --- FROM БИЗНЕС_ЕДИНИЦЫ  WHERE KEY IN (@BUnits)
       UNION ALL  
       SELECT БИЗНЕС_ЕДИНИЦЫ .[KEY], БИЗНЕС_ЕДИНИЦЫ .[CONS_CONTUR_LNK] AS [PARENT_KEY], REC.[LEVEL] + 1 AS [LEVEL]
               FROM REC 
                    JOIN БИЗНЕС_ЕДИНИЦЫ ON REC.[KEY] = БИЗНЕС_ЕДИНИЦЫ .[CONS_CONTUR_LNK]
       )
SELECT [KEY], [PARENT_KEY], [LEVEL] INTO #BU_CHILD FROM REC;

SELECT DISTINCT
             CR.[KEY],
             CR.[NAME],
             CR.[ORD],
             CR.[NAME_EN_US]
FROM КРЕДИТНЫЕ_СОГЛАШЕНИЯ AS LA
       JOIN #BU_CHILD ON LA.[BUSINESS_UNIT] = #BU_CHILD.[KEY]
       JOIN КРЕДИТОРЫ AS CR ON LA.[CREDITOR] = CR.[KEY]



А что более интересно, так это то, что Вариант 1 работает на 10-20% быстрее, несмотря на ТО, что там курсор бегает, да и еще и через функцию, которая работает тоже через функцию в которой цикл стоит. )))

Надеюсь кому-то помогут мои скрипты.
23 июл 15, 13:23    [17925426]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Glory
Member

Откуда:
Сообщений: 104751
НиколайСН
, и я их решил.

А вы раскроете тайну, как where [KEY] in (@BUnit) будет работать для '1, 2, 3, 4, 5, 6' ?
23 июл 15, 13:27    [17925447]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
НиколайСН,

Во-первых, скрипты нерабочие, ибо @BUnit и @BUnits не объявлены.
Во вторых, [KEY] in (@BUnit) - это то же самое, что и [KEY] = @BUnit
23 июл 15, 13:28    [17925452]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Jaffar
Member

Откуда:
Сообщений: 633
НиколайСН,

т.е. ты все кати меняешь код приложения так?
тогда зачем километры всего этого говна если можно просто поменять тип параметра функции и чуть подправить внутри?!!!
23 июл 15, 14:59    [17925828]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
НиколайСН
а при множественной отметки передаётся множество типа "1,2,3,4,5"
Вы раскройте, что вы имеете в виду под словом "множество".
Строка, что ли?
Этого от вас и добиваются.

Как это выглядит в профайлере? Просто скопируйте сюда строки из профайлера, когда "из соответствующей платформы BI ... при множественной отметки передаётся множество типа "1,2,3,4,5"[/quote]
23 июл 15, 15:07    [17925882]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
Glory,
имеется таблиц БИЗНЕС_ЕДИНИЦЫ.

т.к. в перенной @BUnits у меня на входе передается массив типа in (1,2,3,4,5...), где @BUnits=1,2,3,4,5...,
это и есть те самые ключи самих Бизнес-единиц.
Чтобы их перевернуть и потом повесить курсор, я и начитываю этиже сами Бизне-единицы из этой же таблицы, тоесть было:
1,2,3,4,5, ...

стало:
key
1
2
3
4
5
...

незнаю, понятно ли я написал...
23 июл 15, 15:10    [17925906]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
НиколайСН
Glory,
имеется таблиц БИЗНЕС_ЕДИНИЦЫ.

т.к. в перенной @BUnits у меня на входе передается массив типа in (1,2,3,4,5...), где @BUnits=1,2,3,4,5...,
это и есть те самые ключи самих Бизнес-единиц.
Чтобы их перевернуть и потом повесить курсор, я и начитываю этиже сами Бизне-единицы из этой же таблицы, тоесть было:
1,2,3,4,5, ...

стало:
key
1
2
3
4
5
...

незнаю, понятно ли я написал...
массив - это переменная типа int, да? :))
23 июл 15, 15:15    [17925938]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
НиколайСН,

Ещё раз:
[KEY] in (@BUnit)
это то же самое, что и
[KEY] = @BUnit
Где Вы здесь увидели массив??
23 июл 15, 15:16    [17925947]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
alexeyvg,
я не знаю как уже объяснить!
имеется НСИ Бизнес-единиц, которая представлена в виде иерархии типа
Имя,ключ,порядок, Родитель
Соответственно выстраивается иерархия в платформе по принципу Ключ и Родитель. Если Родитель = null, то это верхний уровень.
Получается чтото типа иерархичного, где у "Ребенок" Родитель = ключ от "Мамы"
Бабушка (key=1)
    Мама (key=14)
       Ребенок (key=27)
Дедушка (key=3)


Имеется функция типа AllCreditorsForBUnit(@BUnit,GETDATE()) или же AllCreditorsForBUnit(1,GETDATE())
При выборе в платформе "Бабушка" то выводятся все её кредиторы,
Если Выбрать "Бабушка" и "Дедушка", то этот получаем AllCreditorsForBUnit(1,3,GETDATE()) и вот тут ф-ция выдаёт ошибку, что логично, т.к. параметров становится больше!
Логично предположить что в параметре @BUnit передаётся 1 либо 1,3

Я могу только оперировать @BUnit, я не знаю сколько там передастся ключей тех самых Бизнес-единиц.
Поэтому я их сначала начитываю в таблицу временную, а затем курсором для каждой БЕ начитываю еще в одну временную таблицу всех кредиторов для каждой БЕ.
В итоге много дублей, и distinct делает дальше своё дело.

Сообщение было отредактировано: 23 июл 15, 15:31
23 июл 15, 15:27    [17926020]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
iap
НиколайСН,

Ещё раз:
[KEY] in (@BUnit)
это то же самое, что и
[KEY] = @BUnit
Где Вы здесь увидели массив??


Дело в том что
[KEY] in (1,3,5,7)

это НЕ то же самое, что и
[KEY] = 1,3,5,7

А вот в @BUnit мне может прилететь как и 1, так и целое 1,2,3,4...
23 июл 15, 15:32    [17926053]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
ну блин, вот в таком мне исполнении прилетает @BUnit.
А вот сделать так, чтобы она из Платформы прилетала ко мне в другом варианте я не могу!
скажем так, что @BUnit это не просто int прилетает, а целый массив INT-овых значений через запятую.
23 июл 15, 15:36    [17926085]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Konst_One
Member

Откуда:
Сообщений: 11625
про переменные и их типы вам уже сказали, но вы видимо не поняли.
вот эта AllCreditorsForBUnit ф-я вам доступна для исправления или нет? если да, то смотрите совет, что вам давали про xml-параметр
23 июл 15, 15:36    [17926086]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
НиколайСН
Дело в том что
[KEY] in (1,3,5,7)
Вы написали [KEY] in (@BUnit) и полагаете, что сервер это воспринимает как [KEY] in (1,3,5,7)?
Но я же Вам показал, КАК он это воспринимает!!
Не видите разницу что ли?

Я бы ещё понял, если бы у Вас это было в DSQL, который превращался бы в то,
что Вы написали после подстановки значения переменной.
Но ведь нет!
23 июл 15, 15:37    [17926094]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
Konst_One,
возможно не понял, не отрицаю.
сам пытался по нему пойти, но пришел к вариантам выше.
23 июл 15, 15:38    [17926106]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
iap, мы наверное немного недопоняли и я наверное отчасти неправильно изложил!
@BUnit, это не совсем переменная SQL, это больше переменная в Платформе. Поэтому и не можете понять что это за тип данных такой этот "массив типа IN"
А вот из платформы мне прилетает уже либо 1, либо 1,2,3... , но только я их не вижу, т.к. выбрал в Системе дополнительно пару Бизнес-единиц и запрос автоматом пересчитался и выдал их Кредиторов ))
23 июл 15, 15:46    [17926160]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
Glory
Member

Откуда:
Сообщений: 104751
НиколайСН
А вот из платформы мне прилетает уже либо 1, либо 1,2,3... ,

На самолете прилетает ?
23 июл 15, 15:47    [17926167]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
НиколайСН
ну блин, вот в таком мне исполнении прилетает @BUnit.
А вот сделать так, чтобы она из Платформы прилетала ко мне в другом варианте я не могу!
скажем так, что @BUnit это не просто int прилетает, а целый массив INT-овых значений через запятую.

второй день вас просят сказать @BUnit это int или nvarchar какой нибудь
23 июл 15, 15:48    [17926181]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
НиколайСН
iap, мы наверное немного недопоняли и я наверное отчасти неправильно изложил!
@BUnit, это не совсем переменная SQL, это больше переменная в Платформе. Поэтому и не можете понять что это за тип данных такой этот "массив типа IN"
А вот из платформы мне прилетает уже либо 1, либо 1,2,3... , но только я их не вижу, т.к. выбрал в Системе дополнительно пару Бизнес-единиц и запрос автоматом пересчитался и выдал их Кредиторов ))
То есть прилетает нечто произвольное, а процедура SQL это должно обработать в любом случае?
Причём процедура - чёрный ящик, лезть туда нельзя?
Такая задача, да?
23 июл 15, 15:49    [17926182]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
НиколайСН
Member

Откуда:
Сообщений: 127
Glory
НиколайСН
А вот из платформы мне прилетает уже либо 1, либо 1,2,3... ,

На самолете прилетает ?

PrognozPlatform работает так)) Все вопросы в Пермь на чём они доставляют))
23 июл 15, 15:49    [17926183]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31994
НиколайСН
alexeyvg,
я не знаю как уже объяснить!
Я же говорю, как: скопировать сюда трассу из профайлера.
НиколайСН
скажем так, что @BUnit это не просто int прилетает, а целый массив INT-овых значений через запятую.
Нет, массивов в сиквеле нет.

Можно предположить, что просто вызывается текст, как писал Кот Матроскин, но тогда непонятно, почему отработал код НиколайСН.
23 июл 15, 15:50    [17926187]     Ответить | Цитировать Сообщить модератору
 Re: Create function(array like "in", dt)  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
НиколайСН
@BUnit, это не совсем переменная SQL, это больше


прошу прощения, не с точки зрения чтобы обиенть, но вы не совсем программист, а больше блондинка?
23 июл 15, 15:50    [17926188]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить