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

Откуда:
Сообщений: 232
Есть необходимость сделать запрос к головной таблице, а из подчиненных таблиц нужно выбрать количество записей по ключевому полю. Так как таблиц много, я хотела сделать универсальную функцию, которая бы по параметрам формировала строку, в которой будет подсчитываться количество записей... Написала, но не хочет эта функция работать.... я разбила её на 2, та же засада... Выдает такую ошибку:
Имя "DECLARE myCursor1 CURSOR STATIC FOR SELECT COUNT([КодКотлн]) AS myRecCountObj FROM [dbo].[Т_Приб_УчетаА10] WHERE [Код_Предпр]=260000829 AND [КодКотлн]=260000004" не является допустимым идентификатором.
Вот текст 2 функций:
CREATE FUNCTION [dbo].[CountRecTable]
	(@NameTable nvarchar(50), @KodPotrebit int, @KodObject int)
RETURNS int
AS
BEGIN
    DECLARE @CountRec int
    Set @CountRec=(SELECT [myRecCountObj] FROM [dbo].[MyObjectSubTable](@NameTable,@KodPotrebit,@KodObject))
	return @CountRec
end	

CREATE FUNCTION [dbo].[MyObjectSubTable]
	(@NameTable nvarchar(50), @KodPotrebit int, @KodObject int)
RETURNS 
@retCountRecs TABLE 
(
	-- Add the column definitions for the TABLE variable here
	[myRecCountObj] int 
)
AS
BEGIN
	-- Fill the table variable with the rows for your result set
	declare @strSQL as nvarchar(4000), @CountRecs int
	SET @strSQL=N'DECLARE myCursor1 CURSOR STATIC FOR SELECT COUNT([КодКотлн]) AS myRecCountObj FROM [dbo].['+rtrim(@nametable)+'] WHERE [Код_Предпр]='+ltrim(str(@KodPotrebit))+' AND [КодКотлн]='+ltrim(str(@KodObject))
	EXEC @strSQL
	OPEN myCursor1
	IF @@CURSOR_ROWS>0
		BEGIN 
		   FETCH FROM myCursor1 INTO @CountRecs
		end;
	close myCursor1
	DEALLOCATE myCursor1
	INSERT @retCountRecs SELECT @CountRecs
	
	RETURN 
END

27 июл 09, 10:32    [7462280]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36806
А текст ошибки на мысли не наталкивает?
27 июл 09, 10:40    [7462334]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Glory
Member

Откуда:
Сообщений: 104760
Во-первых, динамические запросы вызываются так EXEC(@strSQL)
Во-вторых, в функциях запрещены динамические запросы
В-третьих, считать количество записей через создание курсора - это бред. Вы еще цикл напишите по всем записям с +1 переменную для каждой итерации

Сообщение было отредактировано: 27 июл 09, 10:42
27 июл 09, 10:40    [7462338]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
Есть необходимость сделать запрос к головной таблице,
а из подчиненных таблиц нужно выбрать количество записей по ключевому полю.
Так как таблиц много, я хотела сделать универсальную функцию...
Много - это десять, да?
И зачем тут функция?
Почему бы просто не написать запрос типа
select m.*, d1.cnt, d2.cnt...
  from master_table as m
  join (select master_id, count(*) as cnt
          from detail_table1
         group by master_id) as d1 on d1.master_id = m.id
  join (select master_id, count(*) as cnt
          from detail_table2
         group by master_id) as d2 on d2.master_id = m.id
...
?

(ну еще where по необходимости подобавлять в основной запрос и в derived tables)
27 июл 09, 10:49    [7462402]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Гавриленко Сергей Алексеевич
А текст ошибки на мысли не наталкивает?

Нет, так как я этот текст сначала проверила отдельно и он работал... Только потом я его вставила в функцию...

Glory

Во-первых, динамические запросы вызываются так EXEC(@strSQL)
Во-вторых, в функциях запрещены динамические запросы
В-третьих, считать количество записей через создание курсора - это бред. Вы еще цикл напишите по всем записям с +1 переменную для каждой итерации

В таком виде
EXEC(@strSQL)
функция просто не создается, идет ошибка....
Я знаю, что создание курсора - это бред...Курсор появился позже...
27 июл 09, 10:54    [7462441]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Glory
Member

Откуда:
Сообщений: 104760
VeroNa

Glory

Во-первых, динамические запросы вызываются так EXEC(@strSQL)
Во-вторых, в функциях запрещены динамические запросы
В-третьих, считать количество записей через создание курсора - это бред. Вы еще цикл напишите по всем записям с +1 переменную для каждой итерации

В таком виде
EXEC(@strSQL)
функция просто не создается, идет ошибка....

Вы ответы читайте полностью, а частями
27 июл 09, 10:55    [7462453]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Паганель
Много - это десять, да?
И зачем тут функция?
Почему бы просто не написать запрос типа
select m.*, d1.cnt, d2.cnt...
  from master_table as m
  join (select master_id, count(*) as cnt
          from detail_table1
         group by master_id) as d1 on d1.master_id = m.id
  join (select master_id, count(*) as cnt
          from detail_table2
         group by master_id) as d2 on d2.master_id = m.id
...
?

(ну еще where по необходимости подобавлять в основной запрос и в derived tables)


Много - это 14.... :)
А вот этот select надо попробовать, спасибо за подсказку!
27 июл 09, 10:55    [7462458]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Паганель
Много - это десять, да?
И зачем тут функция?
Почему бы просто не написать запрос


Спасибо! Наконец-то получается то что нужно...
Вот что получилось на 3 подчиненных таблицах:
SELECT tKot.[КодКотлн] AS myID
	  ,[Код_Предпр]
      ,[НаимКотелн]
      ,[АдресКот]
      ,[Тип_Обкт]
      ,ISNULL(d1.KolPU,0) as KolPU 
      ,ISNULL(d2.KolRT,0) as KolRT
      ,ISNULL(d3.KolITP,0) as KolITP
  FROM [Teplo_Dan_26SQL].[dbo].[Тепл_Котельные] as tKot
  left join (select [КодКотлн], count(*) as KolPU
          from Т_Приб_УчетаА10
          where ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d1 on d1.[КодКотлн] = tKot.[КодКотлн]
  left join (select [КодКотлн], count(*) as KolRT
          from Т_Рег_ТемперА10
          where ISNULL(Т_Рег_ТемперА10.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d2 on d2.[КодКотлн] = tKot.[КодКотлн]
  left join (select [КодКотлн], count(*) as KolITP
          from Т_ЦПТ
          where ISNULL(Т_ЦПТ.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d3 on d3.[КодКотлн] = tKot.[КодКотлн]
  WHERE [Код_Предпр]=260000829
Это именно то, что нужно!!! :)
27 июл 09, 12:12    [7463173]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
VeroNa
Паганель
Много - это десять, да?
И зачем тут функция?
Почему бы просто не написать запрос


Спасибо! Наконец-то получается то что нужно...
Вот что получилось на 3 подчиненных таблицах:
SELECT tKot.[КодКотлн] AS myID
	  ,[Код_Предпр]
      ,[НаимКотелн]
      ,[АдресКот]
      ,[Тип_Обкт]
      ,ISNULL(d1.KolPU,0) as KolPU 
      ,ISNULL(d2.KolRT,0) as KolRT
      ,ISNULL(d3.KolITP,0) as KolITP
  FROM [Teplo_Dan_26SQL].[dbo].[Тепл_Котельные] as tKot
  left join (select [КодКотлн], count(*) as KolPU
          from Т_Приб_УчетаА10
          where ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d1 on d1.[КодКотлн] = tKot.[КодКотлн]
  left join (select [КодКотлн], count(*) as KolRT
          from Т_Рег_ТемперА10
          where ISNULL(Т_Рег_ТемперА10.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d2 on d2.[КодКотлн] = tKot.[КодКотлн]
  left join (select [КодКотлн], count(*) as KolITP
          from Т_ЦПТ
          where ISNULL(Т_ЦПТ.[КодКотлн],0)<>0  
         group by [КодКотлн]) as d3 on d3.[КодКотлн] = tKot.[КодКотлн]
  WHERE [Код_Предпр]=260000829
Это именно то, что нужно!!! :)
Да? Вы уверены? А [Код_Предпр] в какой таблице?
И вообще, хорошим тоном считается писать у полей алиасы таблиц.
27 июл 09, 12:15    [7463192]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
          where ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0
Это такая проверка на IS NOT NULL, или заодно и на ноль?
(я не говорю что это ошибка, просто интересно)
27 июл 09, 12:22    [7463242]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель
VeroNa
          where ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0
Это такая проверка на IS NOT NULL, или заодно и на ноль?
(я не говорю что это ошибка, просто интересно)
Если "заодно и на ноль", то достаточно
Т_Приб_УчетаА10.[КодКотлн]<>0
27 июл 09, 12:32    [7463319]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iap, понимаете... автор по этому делу потом еще и джойнит...
вот я и решил узнать о целях этого условия перед тем как что-то советовать...
может, это условие там вообще не нужно
(а возможно нужно какое-то другое, которое зависит от ответа автора на Ваш вопрос)
27 июл 09, 12:37    [7463365]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
iap

Это именно то, что нужно!!! :)
Да? Вы уверены? А [Код_Предпр] в какой таблице?
И вообще, хорошим тоном считается писать у полей алиасы таблиц.[/quot]
Код_Предпр относится к таблице Тепл_Котельные, поэтому двусмысленности быть не должно, но я учту ваше замечание... :)

Паганель
Это такая проверка на IS NOT NULL, или заодно и на ноль?
(я не говорю что это ошибка, просто интересно)

Это издержки отсутствия видения заказчиком того, что же он хочет получить, да и смены программиста... Поэтому в данных может быть значение NULL... А при соединении это значение лучше пока исключить для правильной работы запросы... Хотя позже придется поставить перед руководством вопрос заполнения этого поля так сказать "ребром"...
27 июл 09, 12:50    [7463492]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
в данных может быть значение NULL...
А при соединении это значение лучше пока исключить для правильной работы запросы...
А разве условие соединения
VeroNa
on d1.[КодКотлн] = tKot.[КодКотлн]
само по себе не исключает эти NULL-ы?
+ репро
declare @m table(id int, name varchar(10))
insert into @m(id, name)
select 1, 'm_1' union all
select 2, 'm_2' union all
select null, 'm_null'

declare @d table(m_id int)
insert into @d(m_id)
select 1 union all
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select null

select m.*, t.cnt
  from @m as m
  left join (select m_id, count(*) as cnt
               from @d
              group by m_id) as t on t.m_id = m.id

id          name       cnt
----------- ---------- -----------
1           m_1        3
2           m_2        2
NULL        m_null     NULL

(3 row(s) affected)
27 июл 09, 12:58    [7463579]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
VeroNa
Это издержки отсутствия видения заказчиком того, что же он хочет получить, да и смены программиста... Поэтому в данных может быть значение NULL... А при соединении это значение лучше пока исключить для правильной работы запросы... Хотя позже придется поставить перед руководством вопрос заполнения этого поля так сказать "ребром"...
Тем не менее, ISNULL из этих условий можно просто выбросить в пользу условия
Т_Приб_УчетаА10.[КодКотлн]<>0
и т.п. NULLы при этом и так отфильтруются.
27 июл 09, 12:58    [7463583]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
секундочку
автор сказал, что ему надо отбросить null-ы
про нули он ничего пока не говорил

автор, так нули отбрасывать или нет ?
27 июл 09, 13:01    [7463606]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
iap, Паганель

В принципе, вы правы... Я просто сначала делала запрос просто на join, а в нем как раз значения NULL не исключались... Но так как меня результат не очень удовлетворил, я переделала запрос на left join, а это соединение исключает NULL...

Большое вам спасибо!
27 июл 09, 13:05    [7463647]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
Я просто сначала делала запрос просто на join, а в нем как раз значения NULL не исключались... Но так как меня результат не очень удовлетворил, я переделала запрос на left join, а это соединение исключает NULL...
Во-первых, это Вы про значения "основной таблицы" написали, а вопрос был про значения "подчиненной таблицы"
во-вторых, Вы все написали с точностью до наоборот
Вот еще одно репро, сравните с предыдущим
+ еще одно репро
declare @m table(id int, name varchar(10))
insert into @m(id, name)
select 1, 'm_1' union all
select 2, 'm_2' union all
select null, 'm_null'

declare @d table(m_id int)
insert into @d(m_id)
select 1 union all
select 1 union all
select 1 union all
select 2 union all
select 2 union all
select null

select m.*, t.cnt
  from @m as m
  join (select m_id, count(*) as cnt
          from @d
         group by m_id) as t on t.m_id = m.id

id          name       cnt
----------- ---------- -----------
1           m_1        3
2           m_2        2

(2 row(s) affected)
27 июл 09, 13:11    [7463707]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель
секундочку
автор сказал, что ему надо отбросить null-ы
про нули он ничего пока не говорил

автор, так нули отбрасывать или нет ?
ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0
оставляет нули?
VeroNa
я переделала запрос на left join, а это соединение исключает NULL
Что Вы имеете в виду?
По-моему, наоборот, LEFT JOIN добавит NULLов в результат по сравнению с INNER JOIN...
27 июл 09, 13:13    [7463731]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iap
ISNULL(Т_Приб_УчетаА10.[КодКотлн],0)<>0
оставляет нули?
нет конечно
вот я и жду от автора ответ - "да, нули действительно не нужны"
а вместо этого получаю ответ, что надо исключить NULL-ы

Это как у Задорнова:
- Скажите, пожалуйста, который час?
- Ой, я ж в институт опаздываю!
27 июл 09, 13:18    [7463780]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Паганель, iap

Ситуация такая, что в таблицах Т_Приб_УчетаА10, Т_Рег_ТемперА10, Т_ЦПТ поле [КодКотлн] может быть равным именно NULL. При соединении left join по полю [КодКотлн] записи со значением NULL в этом поле просто исключатся из запроса, но в результат добавится значение NULL именно для "правильного" значения [КодКотлн]....
Когда я делала запрос просто с join без проверки поля [КодКотлн] на NULL в таблицах Т_Приб_УчетаА10, Т_Рег_ТемперА10, Т_ЦПТ, то запрос суммировал все поля со значением поля [КодКотлн] =Null в них и добавлял результат к первому значению в таблице Тепл_Котельные, которое не может быть NULL НИКОГДА, так как это поле является счетчиком! Получался полный бред....
27 июл 09, 13:24    [7463828]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Паганель,

Не нужны ни нули, ни NULL-ы....
27 июл 09, 13:27    [7463853]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
Когда я делала запрос просто с join без проверки поля [КодКотлн] на NULL в таблицах Т_Приб_УчетаА10, Т_Рег_ТемперА10, Т_ЦПТ,
то запрос суммировал все поля со значением поля [КодКотлн] =Null в них и добавлял результат к первому значению в таблице Тепл_Котельные
Хм, со слов сложно понять...
Не могли бы Вы проиллюстрировать на примере?
(Можете переделать мое репро под свою ситуацию)
27 июл 09, 13:29    [7463870]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
VeroNa
Не нужны ни нули, ни NULL-ы....
Тогда iap прав
27 июл 09, 13:30    [7463880]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по функции  [new]
VeroNa
Member

Откуда:
Сообщений: 232
Паганель,

Это просто в первоначальном запросе ошибка была... Поэтому получался полный бред... Извините, что ввела вас в заблуждение...
27 июл 09, 13:41    [7463982]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить