• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 150
  • Last Modified:

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].[PO_ID] = [Po]
      and [ACCOUNTSPAYABLE_DETAIL].[ITEM_] = [Item#]
                   
      select
            *
      from @PoHistory
0
Greg Rowland
Asked:
Greg Rowland
  • 9
  • 5
  • 2
  • +1
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
Please change update statement to below and try again.

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

Open in new window

0
 
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAuthor Commented:
Thank you Pawan Kumar, reply ASAP midnight CST here, time for a little rest. G
0
 
Pawan KumarDatabase ExpertCommented:
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

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Inner join can also be used ...another way..
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

Open in new window

0
 
Ryan ChongCommented:
Msg 512, Level 16, State 1, Line 59

your codes look ok...which line is line 59?
0
 
Pawan KumarDatabase ExpertCommented:
@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.
0
 
Ryan ChongCommented:
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_]
0
 
Ryan ChongCommented:
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_]
0
 
Pawan KumarDatabase ExpertCommented:
@Ryan -
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)

Open in new window

0
 
Ryan ChongCommented:
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#]

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@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.
0
 
Ryan ChongCommented:
I think someone is getting aggressive here but it really doesn't matter.

i'm not sure what it mean by >> your query will give same data for each combination of [Po],[Item#]). but it's ok as it really doesn't matter now.

this is what i have and i got my stand.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ACCOUNTSPAYABLE_DETAIL]') AND type in (N'U'))
DROP TABLE [dbo].[ACCOUNTSPAYABLE_DETAIL]
GO

CREATE TABLE [dbo].[ACCOUNTSPAYABLE_DETAIL](
	[PO_ID] [int] NULL,
	[CHECKNUMBER] [nchar](10) NULL,
	[PAID_DATE] [datetime] NULL,
	[RECORD_] [nchar](10) NULL,
	[ITEM_] [float] NULL,
	[TOTCOST] [money] NULL
) ON [PRIMARY]

INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 5, 15.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 322, 4.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 3, 43.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (4444, N'0         ', NULL, N'y         ', 5, 5.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (33, N'0         ', NULL, N'y         ', 3, 634.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 4, 5444.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'y         ', 2, 423.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N't44       ', 3, 53.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'4         ', NULL, N'53        ', 2, 5.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'22        ', 5, 23.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'34        ', 443, 1.0000)
INSERT [dbo].[ACCOUNTSPAYABLE_DETAIL] ([PO_ID], [CHECKNUMBER], [PAID_DATE], [RECORD_], [ITEM_], [TOTCOST]) VALUES (99308, N'0         ', NULL, N'33        ', 443, 10.0000)


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TVENDPOD]') AND type in (N'U'))
DROP TABLE [dbo].[TVENDPOD]
GO

CREATE TABLE [dbo].[TVENDPOD](
	[PO_ID] [int] NULL,
	[ITEM_] [float] NULL,
	[QTY] [float] NULL,
	[RECVDQTY] [float] NULL,
	[TOTCOST] [money] NULL,
	[AuthorizedAmount] [money] NULL,
	[RECORD_] [nchar](10) NULL
) ON [PRIMARY]


INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 2, 34, 33, 4534.0000, 3.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 443, 45, 56, 434.0000, 53.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 43, 35, 4, 7.0000, 44.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (533422, 23, 4, 2, 53.0000, 23.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 433, 35, 75, 100.0000, 44.0000, N'y         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 7, 4, 434, 44.0000, 54.0000, NULL)
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 5, 222, 43, 80.0000, 43.0000, N'3         ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 54, 2, 24, 1.0000, 2.0000, N'43        ')
INSERT [dbo].[TVENDPOD] ([PO_ID], [ITEM_], [QTY], [RECVDQTY], [TOTCOST], [AuthorizedAmount], [RECORD_]) VALUES (99308, 3, 4, 332, 2.0000, 3.0000, N'5         ')


declare @Po as int
  set @Po = 99308

 select   [PO_ID], [ITEM_], sum( [TOTCOST]) [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_]
			order by [PO_ID], [ITEM_]
                
  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
            and [ITEM_] = a.[ITEM_]
            )
      from @PoHistory, [ACCOUNTSPAYABLE_DETAIL] a
      where a.[PO_ID] = [Po]
      and a.[ITEM_] = [Item#]

      select
            *
      from @PoHistory 
     order by Po, [Item#]

Open in new window


Untitled.png
just try to solve it using existing method, rather than introduce new methods which asker may not familiar with.
0
 
Pawan KumarDatabase ExpertCommented:
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.

--
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
--

Open in new window


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.
0
 
Scott PletcherSenior DBACommented:
Simply:

update po
set [PendingAmount] = apd.TOTCOST
from @PoHistory po
inner join (
    select PO_ID, ITEM_, sum([TOTCOST]) AS 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_]
) as apd on apd.PO_ID = po.Po and apd.ITEM_ = po.Item#
where po.Po = @Po
0
 
Pawan KumarDatabase ExpertCommented:
I had already given the same before , See this comment ( #a42396448 ) :)

https://www.experts-exchange.com/questions/29072685/Subquery-returned-more-than-1-value-This-is-not-permitted-when-the-subquery-follows-or-when-the-subquery-is-used-as-an-expression.html?anchor=a42397086¬ificationFollowed=201283630#a42396448

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]

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Author - you can choose from the two of my suggestion-


--

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

--

Open in new window


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#]

--

Open in new window

0
 
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerAuthor Commented:
Thank you, everyone, for looking at this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 9
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now