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

Откуда:
Сообщений: 271
Уже голову сломал..
При выполнении запроса возникает ошибка
Сообщение 512, уровень 16, состояние 1, строка 5
Вложенный запрос вернул больше одного значения. Это запрещено, когда вложенный запрос следует после =, !=, <, <=, >, >= или используется в качестве выражения.
Внимание! Значение NULL исключено в агрегатных или других операциях SET.

Запрос
DECLARE @date1 date, @date2 date
SET @date1 = '20140101'
SET @date2 = '20141231'

SELECT
a.Р,a.Ц
,a.Виды
,a.[Нач]
,a.[Начгод]
,a.З
,a.[ЧП]
,a.Акв
,a.,a.Воз
,a.КУ
,a.[КУ+КВ]
,a.Количество
,a.[Средняя]
,(a.З-a.Акв-a.Воз) as 'результат'
 FROM (
  SELECT  
   t.lb2 as'Р'
  ,t.LB3 as'Ц'
  ,v.vid as'Виды'
  ,t.vid
  ,sum(t.[76])  as 'Нач'
  ,(select sum(t.[76]) FROM [data].[dbo].[76] o where o.lb3=t.lb3 and o.vid=t.vid and o.DateBegin between @date1 and @date2) as 'Начгод'
  ,(select sum(p.Z) from (select u.ContractNumber, sum(u.[76])/datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin,case when getdate()>u.dateend then u.dateend else getdate()end) as'Z'
  FROM [data].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 group by u.ContractNumber,u.datebegin,u.dateend) p) as 'З'
  ,sum(t.[76])*(1-(isnull(sum(SumKB),0)/sum(t.[76]))) as 'ЧП'
  ,isnull(sum(SumKB),0) as 'Акв'
  ,sum(SumKB)/sum(t.[76]) as 'KB'
  ,isnull(sum(SumCompensation),0) as 'Воз'
  ,isnull(sum(SumCompensation),0)/sum(t.[76]) as 'КУ'
  ,isnull(sum(SumKB),0)/sum(t.[76])+isnull(sum(SumCompensation),0)/sum(t.[76]) as 'КУ+КВ'
  ,(select count(distinct m.ContractNumber) FROM [SAO1Cdata].[dbo].[76] m where m.lb3=t.lb3 and m.vid=t.vid and m.DateContract between @date1 and @date2) as 'Количество'
  ,sum(t.[76])/(select count(distinct m.ContractNumber) FROM [SAO1Cdata].[dbo].[76] m where m.lb3=t.lb3 and m.vid=t.vid and m.DateContract between @date1 and @date2) as 'Средняя'
  FROM [data].[dbo].[76] t
	 left join [data].[dbo].[kb] k on t.contractnumber=k.ContractNumber
	 left join [data].[dbo].[loss_opl] l on  t.contractnumber=l.ContractNumber
	 left join [Plans].[dbo].[vid_mask] v on t.vid like v.mask
  WHERE t.DatePosting between @date1 and @date2 
  GROUP BY
   t.lb2
  ,t.LB3
  ,v.vid
  ,t.vid
  ) a


На какую именно конструкцию ругается?
24 фев 15, 16:56    [17306312]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37061
На подзапрос с group by.

as 'З' который.

Сообщение было отредактировано: 24 фев 15, 16:59
24 фев 15, 16:59    [17306337]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4259
2viper2viper
На какую именно конструкцию ругается?

Навскидку на эту
(select sum(p.Z) from (select u.ContractNumber, sum(u.[76])/datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin,case when getdate()>u.dateend then u.dateend else getdate()end) as'Z'
  FROM [data].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 group by u.ContractNumber,u.datebegin,u.dateend) p) as 'З'

Закомментируйте и проверьте
24 фев 15, 16:59    [17306342]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4259
Гавриленко Сергей Алексеевич, обошел на кнопке
24 фев 15, 17:01    [17306354]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Чего-то мне кажется, что с этим подзапросом всё в порядке - ведь он возвращает одно число
во внешний запрос.
А что касается p, в котором GROUP BY, то почему бы ему и не вернуть несколько строк для суммирования?
24 фев 15, 17:17    [17306458]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Надо постепенно комментарить строки в SELECTе, пока он не отработает.
24 фев 15, 17:19    [17306477]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
iap
Чего-то мне кажется, что с этим подзапросом всё в порядке - ведь он возвращает одно число
во внешний запрос.
А что касается p, в котором GROUP BY, то почему бы ему и не вернуть несколько строк для суммирования?


так и есть. ошибка в вычислениях ,a.[Начгод] и ,a.[Средняя]
наверное, не правильно указал связи.
по факту есть три таблицы:
Таблица проводок 76 (она основная в запросе)
Таблица КВ
и Таблица Возмещений.
В каждой их них ключем является номер договора. в каждой таблице может быть более одной записи по каждому номеру договора

По задаче, мне нужно вывести по каждому Р, Ц и Виду весь перечень показателей. Боюсь, что я не правильно связал в ,a.[Начгод] и ,a.[Средняя] эту структуру с вложенными запросами (они соответственно тоже должны выгружать показатели каждому Р, Ц и Виду)...
24 фев 15, 18:40    [17306893]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
вроде разобрался. Ошибка была в строке
Не правильно
 ,(select sum(t.[76]) FROM [data].[dbo].[76] o where o.lb3=t.lb3 and o.vid=t.vid and o.DateBegin between @date1 and @date2) as 'Начгод'

Правильно
 ,(select sum([b]o[/b].[76]) FROM [data].[dbo].[76] o where o.lb3=t.lb3 and o.vid=t.vid and o.DateBegin between @date1 and @date2) as 'Начгод'


Осталось разобраться с проблемой деления на ноль
строки
(select sum(p.Z) from (select u.ContractNumber, sum(u.[76])/datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin,case when getdate()>u.dateend then u.dateend else getdate()end) as'Z'
  FROM [data].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 group by u.ContractNumber,u.datebegin,u.dateend) p) as 'З'

и
sum(t.[76])/(select count(distinct m.ContractNumber) FROM [SAO1Cdata].[dbo].[76] m where m.lb3=t.lb3 and m.vid=t.vid and m.DateContract between @date1 and @date2) as 'Средняя'


нашел функции IFERROR и ISERROR
Но SSMS возникает ошибка о том что имя функции не распознано

Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
В моем случае все значения с ошибкой деления на 0 можно приравнять к 0
В чем может быть ошибка при использовании функций, или может есть вариант обойтись другим способом?...
24 фев 15, 20:07    [17307117]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
2viper2viper
В моем случае все значения с ошибкой деления на 0 можно приравнять к 0

Вместо:
select 1/v

нужно писать:
select case when v <> 0 then 1/v else 0 end


Что бы не повторять сложные выражения в case, используйте подзапросы.
24 фев 15, 20:52    [17307297]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
alexeyvg, Спасибо Большое
24 фев 15, 22:15    [17307595]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
Прошу прощения.. может это элементарные вещи, но с построением case вообще беда..

получился такой монструазный код
  ,(select case when datediff(day,p.datebegin,p.dateend)<>0 then 
   (select sum(r.ZSP) from 
   (select p.ContractNumber, sum(p.[76])/datediff(day,p.datebegin,p.dateend)*datediff(day,p.datebegin,case when getdate()>p.dateend then p.dateend else getdate()end) as'ZSP'
   FROM [SAO1Cdata].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 group by p.ContractNumber)r)
   else sum(p.[76]) end
   from (select *FROM [SAO1Cdata].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2)p group by p.datebegin,p.dateend)  as 'ЗСП'

который к тому же и не выполняется..
прошу помочь преобразовать его в
select case when v <> 0 then 1/v else 0 end
, бо уже к вечеру едет крыша от всего этого....
вот исходник, в котором знаменатель нужно проверить на ноль
(select sum(p.ZSP) from (select u.ContractNumber, sum(u.[76])/datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin,case when getdate()>u.dateend then u.dateend else getdate()end) as'ZSP'
  FROM [SAO1Cdata].[dbo].[76] u where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 group by u.ContractNumber,u.datebegin,u.dateend) p) as 'ЗСП'


Буду премного благодарен
24 фев 15, 22:48    [17307699]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
2viper2viper
вот исходник, в котором знаменатель нужно проверить на ноль

(
	select sum(p.ZSP) 
	from (
		select 	ContractNumber, 
			CASE WHEN [C] <> 0 THEN [76] / [C] ELSE 0 END as [ZSP]
		from (
			select 	u.ContractNumber, 
				sum(u.[76]) as [76],
				datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin, case when getdate()>u.dateend then u.dateend else getdate() end) as [C]
	  		FROM [SAO1Cdata].[dbo].[76] u 
			where u.lb3=t.lb3 
				and u.vid=t.vid 
				and u.DateBegin between @date1 and @date2 
			group by u.ContractNumber,u.datebegin,u.dateend
		) p
	) p
) as 'ЗСП'

А вообще нужно ещё вдумываться в смысл запросов.
Во первых, зачем вам ContractNumber, вы же его не испоьзуете?

Во вторых, вам нужно посчитать сумму, не учитывая вариант, когда в знаменатале 0? Так и внесите это в условие. Вроде так можно, хотя нужно ещё проверить.
(
	select sum(p.ZSP) 
	from (
		select 	sum(u.[76]) / datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin, case when getdate()>u.dateend then u.dateend else getdate() end) as [ZSP]
  		FROM [SAO1Cdata].[dbo].[76] u 
		where u.lb3=t.lb3 
			and u.vid=t.vid 
			and u.DateBegin between @date1 and @date2 
			and datediff(day,u.datebegin,u.dateend)*datediff(day,u.datebegin, case when getdate()>u.dateend then u.dateend else getdate() end) <> 0
		group by u.datebegin,u.dateend
	) p
) as 'ЗСП'
25 фев 15, 01:11    [17308028]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе  [new]
2viper2viper
Member

Откуда:
Сообщений: 271
alexeyvg, я Вам очень Благодарен
Моя ошибка по поводу ContractNumber..

Этот запрос возвращает желаемые данные (только досмотрел, что в расчете длительности нужно добавить день)
(select sum(p.ZSP) 
	 from (
           select sum(u.[76]) / (datediff(day,u.datebegin,u.dateend)+1)*(datediff(day,u.datebegin, case when getdate()>u.dateend then      u.dateend else getdate() end)+1) as [ZSP]
               FROM [SAO1Cdata].[dbo].[76] u 
           where u.lb3=t.lb3 and u.vid=t.vid and u.DateBegin between @date1 and @date2 and datediff(day,u.datebegin,u.dateend) > -1
           group by u.datebegin,u.dateend ) p) as 'ЗСП'
25 фев 15, 11:16    [17308961]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить