Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
This script returns the subject error. Done this sort of thing before, been a while. Snow Blind
/*
Error Message
(16 row(s) affected)
Msg 512, Level 16, State 1, Line 59
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(16 row(s) affected)
*/
declare @Po as int
set @Po = 99308
declare @PoHistory table
(
[ID] [int] identity (1, 1)
,[Po] [int] null
,[Item#] [float] null
,[Qty] [float] null default (0.0)
,[RecQty] [float] null default (0.0)
,[Amount] [decimal] (10, 2) default (0)
,[AuthorizedAmount] [decimal] (10, 2) default (0)
,[PendingAmount] [decimal] (10, 2) default (0)
,[PaidAmount] [decimal] (10, 2) default (0)
)
insert into @PoHistory
(
[Po]
,[Item#]
,[Qty]
,[RecQty]
,[Amount]
,[AuthorizedAmount]
)
(
select
[PO_ID]
,[ITEM_]
,[QTY]
,[RECVDQTY]
,[TOTCOST]
,[AuthorizedAmount]
from [TVENDPOD]
where [RECORD_] is not null
and [PO_ID] = @Po
)
update @PoHistory set [PendingAmount] =
(
select
sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
group by [PO_ID], [ITEM_]
)
from @PoHistory, [ACCOUNTSPAYABLE_DETAIL]
where [ACCOUNTSPAYABLE_DETAIL].[
and [ACCOUNTSPAYABLE_DETAIL].[
select
*
from @PoHistory
/*
Error Message
(16 row(s) affected)
Msg 512, Level 16, State 1, Line 59
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
(16 row(s) affected)
*/
declare @Po as int
set @Po = 99308
declare @PoHistory table
(
[ID] [int] identity (1, 1)
,[Po] [int] null
,[Item#] [float] null
,[Qty] [float] null default (0.0)
,[RecQty] [float] null default (0.0)
,[Amount] [decimal] (10, 2) default (0)
,[AuthorizedAmount] [decimal] (10, 2) default (0)
,[PendingAmount] [decimal] (10, 2) default (0)
,[PaidAmount] [decimal] (10, 2) default (0)
)
insert into @PoHistory
(
[Po]
,[Item#]
,[Qty]
,[RecQty]
,[Amount]
,[AuthorizedAmount]
)
(
select
[PO_ID]
,[ITEM_]
,[QTY]
,[RECVDQTY]
,[TOTCOST]
,[AuthorizedAmount]
from [TVENDPOD]
where [RECORD_] is not null
and [PO_ID] = @Po
)
update @PoHistory set [PendingAmount] =
(
select
sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
group by [PO_ID], [ITEM_]
)
from @PoHistory, [ACCOUNTSPAYABLE_DETAIL]
where [ACCOUNTSPAYABLE_DETAIL].[
and [ACCOUNTSPAYABLE_DETAIL].[
select
*
from @PoHistory
ASKER
Thank you Pawan Kumar, reply ASAP midnight CST here, time for a little rest. G
Complete and formatted code for you -
DECLARE @Po AS INT
SET @Po = 99308
DECLARE @PoHistory TABLE
(
[ID] [int] IDENTITY (1, 1),
[Po] [int] NULL,
[Item] [float] NULL,
[Qty] [float] NULL DEFAULT (0.0),
[RecQty] [float] NULL DEFAULT (0.0),
[Amount] [decimal](10, 2) DEFAULT (0),
[AuthorizedAmount] [decimal](10, 2) DEFAULT (0),
[PendingAmount] [decimal](10, 2) DEFAULT (0),
[PaidAmount] [decimal](10, 2) DEFAULT (0)
)
INSERT INTO @PoHistory ([Po],[Item],[Qty],[RecQty],[Amount],[AuthorizedAmount])
SELECT [PO_ID],[ITEM_],[QTY],[RECVDQTY],[TOTCOST],[AuthorizedAmount]
FROM [TVENDPOD]
WHERE [RECORD_] IS NOT NULL AND [PO_ID] = @Po
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY
(
SELECT
SUM([TOTCOST]) [TOTCOST]
FROM [ACCOUNTSPAYABLE_DETAIL] H
WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item]
AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
) x
Inner join can also be used ...another way..
BUT CROSS APPLY will always be helpful and fast in these kind of cases.
BUT CROSS APPLY will always be helpful and fast in these kind of cases.
UDECLARE @Po AS INT
SET @Po = 99308
DECLARE @PoHistory TABLE
(
[ID] [int] IDENTITY (1, 1),
[Po] [int] NULL,
[Item] [float] NULL,
[Qty] [float] NULL DEFAULT (0.0),
[RecQty] [float] NULL DEFAULT (0.0),
[Amount] [decimal](10, 2) DEFAULT (0),
[AuthorizedAmount] [decimal](10, 2) DEFAULT (0),
[PendingAmount] [decimal](10, 2) DEFAULT (0),
[PaidAmount] [decimal](10, 2) DEFAULT (0)
)
INSERT INTO @PoHistory ([Po],[Item],[Qty],[RecQty],[Amount],[AuthorizedAmount])
SELECT [PO_ID],[ITEM_],[QTY],[RECVDQTY],[TOTCOST],[AuthorizedAmount]
FROM [TVENDPOD]
WHERE [RECORD_] IS NOT NULL AND [PO_ID] = @Po
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
INNER JOIN
(
SELECT
SUM([TOTCOST]) [TOTCOST] , H.[PO_ID],H.[ITEM_]
FROM [ACCOUNTSPAYABLE_DETAIL] H
WHERE [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
GROUP BY H.[PO_ID],H.[ITEM_]
)x ON x.[PO_ID] = p.[Po] AND x.[ITEM_] = P.[Item]
select * from @PoHistory
Msg 512, Level 16, State 1, Line 59
your codes look ok...which line is line 59?
@Ryan -
The update statement is incorrect. The inner query is returning multiple records and we are assigning that to single. So effectively the author is updating single record with his statement but Multiple records needs to be updated.
@Greg -
Have you tried my suggestions? Thank you.
The update statement is incorrect. The inner query is returning multiple records and we are assigning that to single. So effectively the author is updating single record with his statement but Multiple records needs to be updated.
@Greg -
Have you tried my suggestions? Thank you.
The inner query is returning multiple records and we are assigning that to single.
it's returns a Sum, so it shouldn't return multiple records
select
sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
group by [PO_ID], [ITEM_]
wait, you probably right. I didn't see the group by:
select sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
group by [PO_ID], [ITEM_]
select sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
group by [PO_ID], [ITEM_]
@Ryan -
SAMPLE FOR YOU --- MULTIPLE RECORDS
SAMPLE FOR YOU --- MULTIPLE RECORDS
CREATE TABLE testSUM
(
[PO_ID] INT
, [ITEM_] INT
, [TOTCOST] INT
)
GO
INSERT INTO testSUM VALUES ( 1,1,100 ) , ( 1,2,30 ) , ( 1,1,300 ) , (3,4,20)
GO
SELECT SUM([TOTCOST]) [TOTCOST]
FROM testSUM
group by [PO_ID], [ITEM_]
/*------------------------
MULTIPLE RECORDS
------------------------*/
TOTCOST
-----------
400
30
20
(3 row(s) affected)
try this to handle inner query error:
update @PoHistory set [PendingAmount] =
(
select
sum( [TOTCOST])
from [ACCOUNTSPAYABLE_DETAIL]
where [RECORD_] is not null
and [PO_ID] = @Po
and [CHECKNUMBER] = 0
and [PAID_DATE] is null
and [ITEM_] = a.[ITEM_]
)
from @PoHistory, [ACCOUNTSPAYABLE_DETAIL] a
where a.[PO_ID] = [Po]
and a.[ITEM_] = [Item#]
@Ryan - That will give you INCORRECT Result !!
Need to set VALUE for ([Po],[Item#]) , your query will give same data for each combination of [Po],[Item#]).
Also note thats very old way of writing SQL code, you should not use that, We should use JOINs.
I have already gave 2 solutions. BOTH should work fine without any issues.
Need to set VALUE for ([Po],[Item#]) , your query will give same data for each combination of [Po],[Item#]).
Also note thats very old way of writing SQL code, you should not use that, We should use JOINs.
I have already gave 2 solutions. BOTH should work fine without any issues.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry missed the a.item in the inner query but it's inefficient to requery the same tables repeatedly. Simply query once and aggregate. If you go with the repeat query and join method, just imagine if you had 10000 Item values. While that might be unrealistic for this particular sample problem, it's important to think about data scaling.
Apart from that we are only hitting the tables once in the method the group by is also handled internally so that method will really scale well.
We are also using old code and at one point in time we have to upgrade and then this code will not work so you have to change lot of code at that time hence as per my suggestion we should use the latest code methodology.
--
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY
(
SELECT
SUM([TOTCOST]) [TOTCOST]
FROM [ACCOUNTSPAYABLE_DETAIL] H
WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item#]
AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
)x
--Apart from that we are only hitting the tables once in the method the group by is also handled internally so that method will really scale well.
We are also using old code and at one point in time we have to upgrade and then this code will not work so you have to change lot of code at that time hence as per my suggestion we should use the latest code methodology.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Author - you can choose from the two of my suggestion-
OR
--
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
CROSS APPLY
(
SELECT
SUM([TOTCOST]) [TOTCOST]
FROM [ACCOUNTSPAYABLE_DETAIL] H
WHERE H.[PO_ID] = P.[Po] AND H.[ITEM_] = P.[Item#]
AND [RECORD_] is not null
AND [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
)x
--OR
--
UPDATE P
SET P.[PendingAmount] = x.TOTCOST
FROM @PoHistory P
INNER JOIN
(
SELECT
SUM([TOTCOST]) [TOTCOST] , H.[PO_ID],H.[ITEM_]
FROM [ACCOUNTSPAYABLE_DETAIL] H
WHERE [PO_ID] = @Po AND [CHECKNUMBER] = 0 AND [PAID_DATE] IS NULL
AND [RECORD_] is not null
GROUP BY H.[PO_ID],H.[ITEM_]
)x ON x.[PO_ID] = p.[Po] AND x.[ITEM_] = P.[Item#]
--ASKER
Thank you, everyone, for looking at this.
Open in new window