Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Табличная функция, как привязать (ms sql 2008R2)  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Исходные данные:
declare @sh table (ID int, test1 varchar(10), DateInsert smalldatetime)
declare @Customer table (ID int, sh_ID int, test2 varchar(10))

-- табличная функция 
ft_ShCustomerSUM(sh_ID)
-- результат в виде 
CustomerID, AllSum


Была процедура, с параметром @ss
declare @ss int
set @ss =5
--
select 
 sh.ID
,sh.test1
,shcm.AllSum
from @sh sh
inner join @Customer cmp ON sh.ID =cmp.sh_ID
left join ft_ShCustomerSUM(@ss) as shcm ON shcm.CustomerID =cmp.ID
where sh_ID =@ss


--Теперь нужно переписать, чтобы процедура показывала результат за период
DateInsert between @DateStart and @DateEnd


Вопрос: Не понятно, как убрать параметр из табличной функции?
Потому что результат будет по нескольким sh_ID.

Подскажите направление, куда копать?
11 апр 12, 10:47    [12397535]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
cross apply
11 апр 12, 10:57    [12397624]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
invm,
Спасибо, за направление.
CROSS APPLY — возвращает строки, присутствующие одновременно и в таблице, и в функции.
OUTER APPLY — возвращает все строки таблицы независимо от того, возвращаются или нет соответствующие им строки из функции.

У меня LEFT JOIN, значит подходит оператор OUTER APPLY

На условие ON shcm.CustomerID =cmp.ID рядом с OUTER APPLY сервер ругается
Incorrect syntax near the keyword 'ON'.
А если его перенести в WHERE это уже будет как INNER JOIN, а нужно чтобы было как LEFT JOIN. Или я ошибаюсь?

Как мне его написать?
11 апр 12, 11:26    [12397871]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
trew
А если его перенести в WHERE это уже будет как INNER JOIN, а нужно чтобы было как LEFT JOIN. Или я ошибаюсь?

Как мне его написать?
WHERE (shcm.CustomerID is null or shcm.CustomerID = cmp.ID)
11 апр 12, 11:31    [12397913]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
alexeyvg,
Точно, спасибо!
11 апр 12, 11:41    [12398001]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
alexeyvg,
здравствуйте, а вот этот запрос как можно через APPLY?
   FROM
	 dbo.J
           left join 
	dbo.funcK (J.n)K
		ON EXISTS(SELECT J.a,J.b,J.c,J.d
					INTERSECT 
				SELECT K.a,K.b,K.c,K.d)

или тут вместо функции лучше создать временную таблицу?
21 апр 12, 11:07    [12450303]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
ZVER-10,

from
 dbo.J cross apply
 dbo.funcK(J.n) K
where
 exists(select J.a,J.b,J.c,J.d intersect select K.a,K.b,K.c,K.d)
21 апр 12, 11:19    [12450314]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
invm,
я так уже пробовал, не тот результат. так получается как иннер джоин
21 апр 12, 11:25    [12450315]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
ZVER-10
invm,
я так уже пробовал, не тот результат. так получается как иннер джоин
from
 dbo.J outer apply
 dbo.funcK(J.n) K
where
 exists(select J.a,J.b,J.c,J.d intersect select K.a,K.b,K.c,K.d)
21 апр 12, 11:35    [12450325]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Если K.a, K.b, K.c, K.d не допускают null
from
 dbo.J outer apply
 dbo.funcK(J.n) K
where
 exists(select J.a, J.b, J.c, J.d intersect select isnull(K.a, J.a), isnull(K.b, J.b), isnull(K.c, J.c), isnull(K.d, J.d))
21 апр 12, 11:36    [12450326]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
iap,
))так тоже пробовал. не то
21 апр 12, 11:37    [12450327]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
А если допускают null, то добавьте в функцию фейковую не нуловую колонку. Тогда:
from
 dbo.J outer apply
 dbo.funcK(J.n) K
where
 K.fake_column is null or
 exists(select J.a, J.b, J.c, J.d intersect select K.a, K.b, K.c, K.d)
21 апр 12, 11:39    [12450331]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
invm,
J.a,J.b,K.a,K.b-не допускают а
J.c,J.d,K.c,K.d-допускают
21 апр 12, 11:40    [12450332]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
ZVER-10
invm,
J.a,J.b,K.a,K.b-не допускают а
J.c,J.d,K.c,K.d-допускают
from
 dbo.J outer apply
 dbo.funcK(J.n) K
where
 (K.a is null and K.b is null and K.c is null and K.d is null) or
 exists(select J.a, J.b, J.c, J.d intersect select K.a, K.b, K.c, K.d)
или
from
 dbo.J outer apply
 dbo.funcK(J.n) K
where
 coalesce(K.a, K.b, K.c, K.d) is null or
 exists(select J.a, J.b, J.c, J.d intersect select K.a, K.b, K.c, K.d)
21 апр 12, 11:44    [12450338]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
invm,iap
спасибо ребята ОГРОМНОЕ!
заработали оба варианты предложенные invm
21 апр 12, 11:56    [12450351]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
iljy
Member

Откуда:
Сообщений: 8711
ZVER-10,

правильнее всего так:
select * from
 dbo.J outer apply (
  select *
  from dbo.funcK(J.n) K
  where exists(select J.a,J.b,J.c,J.d intersect select K.a,K.b,K.c,K.d)
)K
21 апр 12, 15:56    [12450830]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
iljy,
мда, так оказывается правильнее, не сразу понял.
спасибо огромное!
22 апр 12, 02:20    [12452127]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
ZVER-10,
в моем примере так работает намного шустрее
автор
DECLARE @n <type>
.....
FROM
dbo.J
left join
dbo.funcK (@n)K
ON EXISTS(SELECT J.a,J.b,J.c,J.d
INTERSECT
SELECT K.a,K.b,K.c,K.d)
22 апр 12, 02:37    [12452136]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
ZVER-10,
извиняюсь, так думаю нагляднее
DECLARE @n <type>
.....
FROM
    dbo.J
  left join
    dbo.funcK (@n)K
        ON EXISTS(SELECT J.a,J.b,J.c,J.d
                          INTERSECT
                              SELECT K.a,K.b,K.c,K.d)
22 апр 12, 02:41    [12452138]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
iljy
Member

Откуда:
Сообщений: 8711
ZVER-10
ZVER-10,
в моем примере так работает намного шустрее
автор
DECLARE @n <type>
.....
FROM
dbo.J
left join
dbo.funcK (@n)K
ON EXISTS(SELECT J.a,J.b,J.c,J.d
INTERSECT
SELECT K.a,K.b,K.c,K.d)

Эммм... а ничего, что в этом случае вызов функции осуществляется один раз для одного значения, а не для каждой строки первой таблицы??
22 апр 12, 10:57    [12452345]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
iljy
Эммм... а ничего, что в этом случае вызов функции осуществляется один раз для одного значения, а не для каждой строки первой таблицы??

как раз так и надо в моем примере.
DECLARE @Data datetime
SELECT @Data= data from j where id=1234
FROM
    dbo.J
  left join
    dbo.funcK (@n)K
        ON EXISTS(SELECT J.a,J.b,J.c,J.d
                          INTERSECT
                              SELECT K.a,K.b,K.c,K.d)

извиняюсь за то что нормально не смог поставить вопрос. мало сплю и в голове бардак. но решил проблему и всем спс :)
22 апр 12, 18:12    [12453118]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
ZVER-10,
извините ради бога за флуд, но крыша едет уже:). вот так:
DECLARE @Data datetime
SELECT @Data= data from j where id=1234
.......
FROM
    dbo.J
  left join
    dbo.funcK (@Data)K
        ON EXISTS(SELECT J.a,J.b,J.c,J.d
                          INTERSECT
                              SELECT K.a,K.b,K.c,K.d)
22 апр 12, 18:15    [12453125]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
ZVER-10,
может ща нарушаю правила форума и продолжаю открытую мною тему в другом месте. но ту тему закрыли из-за того что там с одним хорошим человеком мы начали говорить не нужные слова. если считайте что это кростопик, скжите и не стану продолжать.
ворпрос, а как избавиться от цикла.может СТЕ или есть еще вариант боле правильнее и быстрее для моего примера. ща у меня есть триггер. логика такова: есть таблица Jurnal(приведу только нужные поля):
+
CREATE TABLE dbo.Jurnal(
	Kod int IDENTITY(1,1) NOT NULL,
	KodT int NULL,
	KodAB int NOT NULL,
	Price decimal(18, 2)NOT NULL ,
	Srok datetime NULL,
	SerNumber char(50) NULL,
        Data datetime NOT NULL,
	Kol decimal(18, 2) NOT NULL ,
	KodOtv1 int NULL,
	KodOtv2 int NULL,
	Doc int NULL --тип документа(например 5-переоценка)

и есть вспомогательная таблица dbo.KodT для вычисление поле dbo.Jurnal(KodT):
+

просто направьте. спс)
CREATE TABLE [dbo].[KodT]
(
	[id_KodT] [int] IDENTITY(1,1) NOT NULL,
	[KodAB] [int] NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[Srok] [datetime] NULL,
	[SerNumber] [char](50) ,
	[Data] [datetime] NOT NULL,
	[KodT] [int] NOT NULL
) 

при вставке в dbo.Jurnal, вычисляем KodT след образом: ищем совпадение в dbo.KodT по след.полям. KodAB,Price,Srok,SerNumber. если нету такого, тогда установим dbo.KodT(max(KodT))+1
повесил вот такой триггер:
+
create trigger [tr_Insert] on [dbo].[Jurnal]
after insert
as
BEGIN
SET NOCOUNT ON

declare @next_kod	int--переменная чтоб пройтись по строкам. т.е. сравнивая с Kod=========================
declare @data datetime
--тут объявляем временную таблицу и заполняем из Табл.Инсертед===============================================
create table #Jurnal(
	 Kod		int primary key not null
	,KodT		int
	,KodAB		int
	,Price		decimal(18,2)
	,Srok		datetime
	,SerNumber	char(50)
	,Data		datetime
	,kodOtv1	int
	,kodOtv2	int
	,kol		decimal(18,2)
	,summa		decimal(18,2)
	,Doc		int
) 

CREATE NONCLUSTERED INDEX [IX_Jur] ON [dbo].[#Jurnal] 
(
	[KodAB] ASC,
	[Price] ASC,
	[Srok] ASC,
	[SerNumber] ASC
)



insert #Jurnal (
	 Kod
	,KodT
	,KodAB
	,Price
	,Srok
	,SerNumber
	,Data
	,kodOtv1	
	,kodOtv2	
	,kol		
	,summa		
	,Doc
)
select
	 Kod
	,KodT
	,KodAB
	,Price
	,Srok
	,SerNumber
	,Data
	,kodOtv1	
	,kodOtv2	
	,kol		
	,summa		
	,Doc
from
	inserted


	while exists(select * from #Jurnal)--если табл. имеет строку=================================
	BEGIN--1
		select
			@next_kod = min(kod)
		from
			#Jurnal
		declare @KodT int;
		set @KodT=0
		--если это переоценка т.е. Doc=5==========================================================
		--просто добавляем эту строку в табл. dbo.KodT. 
		IF exists(select 1 from #jurnal where Kod=@next_kod and Doc=5)--1	
			BEGIN--1.1
				insert dbo.KodT
				select KodAB,Price,Srok,SerNumber,Data,KodT
					from #Jurnal
						where Kod=@next_kod
		--=========================================================================================
				DELETE
					#Jurnal
				WHERE
					Kod = @next_kod	
				-------------------
				CONTINUE
		
			END--1.1
		--если не переоценка========================================================================
		--ищем совпадение в табл dbo.KodT через функции fKodT и устанавливаем значение @KodT
		ELSE--1
			BEGIN--1.2
				
						set @data=(select top 1 data from #jurnal where Kod = @next_kod)
						SELECT top 1 @KodT=
							isnull(
								nullif(K.KodT,0)
									,isnull((select max(nullif(kodT,0)) from dbo.KodT),0)+1
									)
							FROM
								#Jurnal J
							left join 
								dbo.fKodT(@data)K
									ON EXISTS(SELECT J.KodAB,J.Price,J.Srok,J.SerNumber 
										INTERSECT 
											SELECT K.KodAB,K.Price,K.Srok,K.SerNumber)
								WHERE
									J.Kod = @next_kod
									ORDER BY K.Data DESC


		--если небыло совпадение в табл.dbo.KodT т.е. @KodT присвоился (max(KodT)from dbo.KodT)+1  тогда
		--добавим эту строку в dbo.KodT
		
						IF NOT EXISTS(SELECT 1 FROM dbo.KodT WHERE KodT=@KodT)--1.1.1 
							BEGIN--1.2.1.1
								INSERT dbo.KodT(KodAB,Price,Srok,SerNumber,Data,KodT)
								SELECT KodAB,Price,Srok,SerNumber,Data,@KodT
									FROM #Jurnal
										WHERE Kod=@next_kod
							END--1.2.1.1
							
				
			END--1.2	
-------------------------------------------------------------------------------------------

		update Jurnal
			set	KodT =@KodT
			where Kod = @next_kod

--------------------------------------------------------------------------------------------

		DELETE
			#Jurnal
		WHERE
			Kod = @next_kod	
				
	END--1

END

а вот функция dbo.fKodT:
+

CREATE FUNCTION

    [dbo].[fKodT](@Data DATETIME)

RETURNS table
AS

RETURN (SELECT KodT,KodAB,Price,Srok,SerNumber,MAX(DATA)DATA 
			FROM dbo.KodT 
				where Data<@Data
					GROUP BY KodT,KodAB,Price,Srok,SerNumber)
23 апр 12, 10:12    [12455148]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
ZVER-10,
можно и без примера, просто направьте куда копать
23 апр 12, 12:16    [12455948]     Ответить | Цитировать Сообщить модератору
 Re: Табличная функция, как привязать (ms sql 2008R2)  [new]
ZVER-10
Member

Откуда:
Сообщений: 506
up
23 апр 12, 19:32    [12459163]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить