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

Откуда:
Сообщений: 26
Есть две таблицы: "отдыхающий" и "комната". У отдыхающего есть битовое поле "Выселение" и триггер, который, при изменении этого поля на true, прибавляет к значению поля "Кол-во свободных мест" в таблице "комната" единицу. При изменении этого поля вручную все работает. Но если использовать хранимую процедуру, которая просматривает таблицу "отдыхающий" и выселяет тех, у кого время пребывания закончилось, sql выдает ошибку "вложенный запрос вернул больше одного значения".
По отдельности хранимая и триггер работают, а вместе не хотят

Код хранимой:
ALTER PROCEDURE [dbo].[UpdateKomn]
AS
begin
UPDATE SANAT.Отдыхающий
SET Выселен = 1
where dateadd(day,Кол_во_дней_пребывания,Дата_вселения) < GETDATE() and Выселен<>1
end


Код трггера:
ALTER TRIGGER [SANAT].[UdalKomn]
ON [SANAT].[Отдыхающий]
FOR Update
AS

DECLARE @SVOB_mest int

IF Exists (Select * FROM Комната, Отдыхающий, inserted where inserted.Выселен = 1 and
Отдыхающий.Номер_санаторно_курортной_книжки = inserted.Номер_санаторно_курортной_книжки and
Отдыхающий.Номер_комнаты=Комната.Номер_комнаты)

Begin
SELECT @SVOB_mest = Кол_во_свободных_мест
FROM SANAT.Комната
WHERE SANAT.Комната.Номер_комнаты =(SELECT Номер_комнаты FROM inserted)
SET @SVOB_mest = @SVOB_mest+1
UPDATE SANAT.Комната SET Кол_во_свободных_мест = @SVOB_mest where SANAT.Комната.Номер_комнаты =(SELECT Номер_комнаты FROM inserted)
END
11 апр 16, 20:25    [19044562]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

ну все правильно он вам говорит. Триггер у вас заточен строго на обработку одной записи, а процедура может поменять сразу несколько, поэтому триггер валится. Перепишите его на табличную обработку.
11 апр 16, 20:36    [19044603]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

Откуда:
Сообщений: 26
iljy,
iljy
Перепишите его на табличную обработку.

не совсем понял, объясни плиз, а то голова уже не варит
11 апр 16, 20:45    [19044631]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

SANAT.Комната.Номер_комнаты =(SELECT Номер_комнаты FROM inserted)

Здесь будет ошибка, если в inserted больше 1 записи. Условие надо переписать через JOIN|IN|EXISTS по выбору. Дальше, в переменной @SVOB_mest может быть только одно значение, если записей будет несколько - это тоже не прокатит. Надо объединить запросы SELECT и UPDATE, и тоже через множественные операции.
11 апр 16, 21:09    [19044719]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

Откуда:
Сообщений: 26
iljy,
iljy
Надо объединить запросы SELECT и UPDATE, и тоже через множественные операции.


я sql совсем недавно начал изучать, можешь объяснить на пальцах?)
11 апр 16, 22:33    [19044939]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
Glory
Member

Откуда:
Сообщений: 104751
belya0695
я sql совсем недавно начал изучать, можешь объяснить на пальцах?)

В команде UPDATE тоже можно использовать больше одной таблицы, соединяя их через JOIN
12 апр 16, 09:08    [19045473]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

ALTER TRIGGER [SANAT].[UdalKomn]
ON [SANAT].[Отдыхающий]
FOR Update
AS

Begin
UPDATE SANAT.Комната
SET Кол_во_свободных_мест = Кол_во_свободных_мест+1
From SANAT.Комната
Inner Join inserted ON inserted.Номер_комнаты = Комната.Номер_комнаты
Where inserted.Выселен = 1
END

сделал так, процедура теперь выполняется, но сейчас появилась новая проблема: в одной комнате живут два отдыхающих, которых пора выселить. хранимая процедура ставит отметки о выселении обоим, но в таблице "Комната" в этой комнате появляется только одно свободное место
12 апр 16, 19:44    [19049208]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
aleks2
Guest
ALTER TRIGGER [SANAT].[UdalKomn]
ON [SANAT].[Отдыхающий]
FOR Update
AS 

Begin 

  with t as ( select Номер_комнаты, count(*) as ОсвободМеста from inserted where Выселен = 1 group by Номер_комнаты)
  update x SET Кол_во_свободных_мест = Кол_во_свободных_мест + t.ОсвободМеста 
    from SANAT.Комната as x  Inner Join t ON t.Номер_комнаты = x.Номер_комнаты

END
12 апр 16, 20:13    [19049267]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
Glory
Member

Откуда:
Сообщений: 104751
belya0695
хранимая процедура ставит отметки о выселении обоим, но в таблице "Комната" в этой комнате появляется только одно свободное место

Потому что надо сначала суммировать высиляемых по номеру комнаты.
12 апр 16, 20:14    [19049269]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

Откуда:
Сообщений: 26
Ребята, все заработало. Спасибо огромное.
Хочу попросить помочь еще с одной штукой, которая мне не по силам.
Нужна хранимая процедура, которая будет считать премию врачам, в зависимости от кол-ва назначенных ими процедур по формуле:

(общая выручка за месяц)/(кол-во всех процедур)*(кол-во процедур которые назначил врач)*0,06%

Есть представление в котором выводится сколько процедур назначил врач за месяц, оттуда можно взять и общее кол-во процедур за месяц. Но я не могу сообразить как все вместе связать
12 апр 16, 22:10    [19049642]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

для подсчета общего количества процедур можно в запрос к представлению добавить SUM(...) OVER()
12 апр 16, 22:18    [19049665]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

да, это понятно
так получилось что у меня есть курсор который считает общую выручку, можно как-то в хранимой использовать эту переменную с выручкой?
12 апр 16, 22:28    [19049690]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

не понял, о чем вы. Использовать переменную - ради бога, передайте ее как параметр. Но зачем для подсчета суммы курсор?? Ну и потом, я предлагал вариант, позволяющий решить одним запросом.
SELECT Врач, ПроцВрачаЗаМесяц, SUM(ПроцВрачаЗаМесяц) OVER() ПроцЗаМесяцВсего
FROM ВашеПредставление
12 апр 16, 22:37    [19049707]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

у меня новая проблема. почему-то в представлении выводит Кол-во ровно в два раза больше чем есть на самом деле

SELECT     SANAT.Сотрудник.ID_сотрудника, SANAT.Сотрудник.Фамилия, SANAT.Сотрудник.Имя, SANAT.Сотрудник.Отчество, 
                      SUM(SANAT.Назначенные_процедуры.Кол_во) AS Кол_во
FROM         SANAT.Медуслуга INNER JOIN
                      SANAT.Назначенные_процедуры ON SANAT.Медуслуга.Номер_медуслуги = SANAT.Назначенные_процедуры.Номер_медуслуги INNER JOIN
                      SANAT.Сотрудник ON SANAT.Назначенные_процедуры.ID_сотрудника = SANAT.Сотрудник.ID_сотрудника INNER JOIN
                      SANAT.Цены ON SANAT.Медуслуга.Номер_медуслуги = SANAT.Цены.Номер_медуслуги
WHERE     (SANAT.Назначенные_процедуры.Дата_назначения BETWEEN GETDATE() - 30 AND GETDATE())
GROUP BY SANAT.Сотрудник.Фамилия, SANAT.Сотрудник.Имя, SANAT.Сотрудник.Отчество, SANAT.Назначенные_процедуры.Дата_назначения, 
                      SANAT.Сотрудник.ID_сотрудника
12 апр 16, 23:06    [19049764]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

уберите группировку и посмотрите, что вам джойны наджойнили, скорее всего по какой-то прицине ваши условия задваивают строки.
12 апр 16, 23:10    [19049773]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

я пробовал сделать, чтобы выручку еще считало, и добавил таблицу Цены
без нее все работает
12 апр 16, 23:12    [19049778]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

Откуда:
Сообщений: 8711
belya0695
iljy,

я пробовал сделать, чтобы выручку еще считало, и добавил таблицу Цены
без нее все работает


Ну и посмотрите, сколько там цен может быть для одной услуги.
12 апр 16, 23:14    [19049783]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

понял
цены по идее должны обновляться каждый месяц, поэтому в таблице несколько прейскурантов
как сделать чтобы выбирало цены из самого нового прейскуранта?
12 апр 16, 23:30    [19049847]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

Сделал так. Вроде работает

DECLARE @NOMER int
SET @NOMER =(SELECT MAX (Номер_прейскуранта) FROM SANAT.Цены)
SELECT     SANAT.Сотрудник.ID_сотрудника, SANAT.Сотрудник.Фамилия, SANAT.Сотрудник.Имя, SANAT.Сотрудник.Отчество, 
                      SUM(SANAT.Назначенные_процедуры.Кол_во) AS Кол_во, SUM(SANAT.Назначенные_процедуры.Кол_во * SANAT.Цены.Стоимость_1_мед_услуги) 
                      AS выручка
FROM         SANAT.Медуслуга INNER JOIN
                      SANAT.Назначенные_процедуры ON SANAT.Медуслуга.Номер_медуслуги = SANAT.Назначенные_процедуры.Номер_медуслуги INNER JOIN
                      SANAT.Сотрудник ON SANAT.Назначенные_процедуры.ID_сотрудника = SANAT.Сотрудник.ID_сотрудника INNER JOIN
                      SANAT.Цены ON SANAT.Медуслуга.Номер_медуслуги = SANAT.Цены.Номер_медуслуги
WHERE     (SANAT.Назначенные_процедуры.Дата_назначения BETWEEN GETDATE() - 30 AND GETDATE()) AND 
                      (SANAT.Цены.Номер_прейскуранта = @NOMER)
GROUP BY SANAT.Сотрудник.Фамилия, SANAT.Сотрудник.Имя, SANAT.Сотрудник.Отчество, SANAT.Назначенные_процедуры.Дата_назначения, 
                      SANAT.Сотрудник.ID_сотрудника
12 апр 16, 23:36    [19049874]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

но с переменной это уже не представление
12 апр 16, 23:42    [19049891]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

с представлением я разобрался)
теперь хочу сделать хранимую, которая будет считать размер премии и вставлять строки в таблицу "Премия", но не представляю как это сделать. Остановился на этом:

ALTER PROCEDURE [dbo].[PREMIYA]	
AS
BEGIN

DECLARE @koef int
DECLARE @N_prikaza int
DECLARE @count int

Set @count = (SELECT count(ID_сотрудника) FROM dbo.ПроцедурыЗаМесяцИВырычка)
Set @N_prikaza = (SELECT max(Номер_приказа) FROM SANAT.Премия) + 1
Set @koef = (SELECT SUM(выручка) FROM dbo.ПроцедурыЗаМесяцИВырычка)/(SELECT SUM(Кол_во) FROM dbo.ПроцедурыЗаМесяцИВырычка)

END


@koef должен умножаться на Кол_во процедур из ПроцедурыЗаМесяцИВырычка, которые назначил врач
и как-то нужно вставить это в таблицу премия. и так для всех врачей, которые есть в представлении ПроцедурыЗаМесяцИВырычка
13 апр 16, 01:16    [19050095]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
iljy
Member

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

SELECT * -- тут пишете свою формулу
FROM ПроцедурыЗаМесяцИВырычка
   CROSS JOIN (SELECT SUM(выручка) / SUM(Кол_во) koef FROM dbo.ПроцедурыЗаМесяцИВырычка) t
13 апр 16, 04:59    [19050161]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
belya0695
Member

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

все считает правильно, но не могу теперь справится со вставкой в таблицу

ALTER PROCEDURE [dbo].[PREMIYA]
	
AS
BEGIN

DECLARE @koef int
DECLARE @N_prikaza int

Set @N_prikaza = (SELECT max(Номер_приказа) FROM SANAT.Премия) + 1
Set @koef = (SELECT SUM(выручка) FROM dbo.ПроцедурыЗаМесяцИВырычка)/(SELECT SUM(Кол_во) FROM dbo.ПроцедурыЗаМесяцИВырычка)

INSERT INTO SANAT.Премия(Номер_приказа, Размер_премии, Дата, ID_сотрудника)

SELECT  max(Номер_приказа)+ 1 ,Кол_во * @koef * 0.06, GETDATE(), ПроцедурыЗаМесяцИВырычка.ID_сотрудника 
FROM dbo.ПроцедурыЗаМесяцИВырычка, SANAT.Премия
   CROSS JOIN (SELECT SUM(выручка) / SUM(Кол_во) koef FROM dbo.ПроцедурыЗаМесяцИВырычка) t

END




ругается на ПроцедурыЗаМесяцИВырычка.ID_сотрудника
и еще Номер_приказа должен быть одинаковый во всех записях, которые вставляются. мне кажется я сейчас неправильно сделал
13 апр 16, 17:27    [19053032]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
belya0695,
вообще в ошибке всё написано
GROUP BY
13 апр 16, 17:31    [19053043]     Ответить | Цитировать Сообщить модератору
 Re: вложенный запрос вернул больше одного значения  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
belya0695,

автор
и еще Номер_приказа должен быть одинаковый во всех записях, которые вставляются. мне кажется я сейчас неправильно сделал

определяй до вставки в переменную
13 апр 16, 17:32    [19053046]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить