I have a database test today and I'm practicing functions with old exercises only that I'm getting an error when I run the function.
tables
set language brazilian;
CREATE TABLE pedido (
nr_pedido numeric(5) NOT NULL,
dt_pedido date,
nr_mesa numeric(4)
);
CREATE TABLE itens_pedido (
nr_pedido numeric(5) NOT NULL,
nr_prod numeric(5) NOT NULL,
qt_item smallint
);
CREATE TABLE produto (
nr_prod numeric(5) NOT NULL,
ds_produto varchar(60) NOT NULL,
vl_venda numeric(9,2)
);
PS: the primary and foreign keys were set with alter table.
inserts
insert into produto values(1, 'Velho Barreiro', 5.00),
(2, 'Ypioca', 10.00),
(3, 'Casquinha de Caranguejo', 55.00),
(4, 'Calabresa', 15.00),
(5, 'Feijão Tropeiro', 29.50),
(6, 'Muqueca de camarão', 79.50),
(7, 'Coca-Cola', 9.90),
(8, 'Suco de Laranja', 2.50),
(9, 'Macaxeira', 12.30),
(10, 'Soda Limonada', 2.10),
(11, 'Coração de Galinha', 11.20),
(12, 'Vodka Skarloff', 13.70),
(13, 'Caninha 61', 9.90)
INSERT INTO pedido VALUES (1, GETDATE(), 527),
(2, '10/10/2009', 632),
(3, '21/05/2009', 606),
(4, '26/06/2009', 970),
(5, '05/05/2009', 606),
(6, '13/11/2009', 527),
(7, '01/03/2005', 181),
(8, '09/04/2010', 181);
INSERT INTO itens_pedido VALUES (1, 11, 2),
(1, 4, 1),
(2, 3, 15),
(3, 2, 2),
(3, 8, 2),
(4, 1, 3),
(4, 7, 2),
(5, 5, 4),
(5, 6, 2),
(6, 6, 3),
(7, 10, 2),
(7, 9, 4),
(2, 2, 5);
In the exercise you have the following question:
2) Show the total value of the request passed as a parameter through a function.
Then I created the following function:
create function ex_f2(@ped numeric(5))
returns numeric(8,2)
as
begin
declare @total numeric(8,2)
set @total = (select pr.vl_venda * i.qt_item
from pedido p inner join itens_pedido i
on p.nr_pedido = i.nr_pedido inner join produto pr
on pr.nr_prod = i.nr_prod
where p.nr_pedido = @ped)
return (@total)
end
But when running with select:
select dbo.ex_f2 (1)
Here is the error:
Msg 512, Level 16, State 1, Line 114 The subquery returned more than 1 value. This is not allowed when the subquery follows a =,! =, & Lt ;, < = , & gt ;, > = or when it is used as an expression.
PS2 .: This is the function that the teacher created at the time of correcting the exercise (it gives the same error):
create function ex_f2(@pedi numeric(5))
returns numeric(8,2)
as
begin
return (select i.qt_item * pr.vl_venda 'valor total'
from pedido p inner join itens_pedido i on p.nr_pedido = i.nr_pedido
inner join produto pr on pr.nr_prod = i.nr_prod
where p.nr_pedido = @pedi)
end