Subquery returned more than 1 value

[hrg]

I've written an SQL statement to update a database as follows:

UPDATE Agent SET previousBalanceDue = (select isnull(dbo.fnGetAgentTotalBalanceDue(taxIdNumber),0))

The statement fails with the follwing message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


If I run only the innner select query using a string literal the function returns a single value, i.e.:

select isnull(dbo.fnGetAgentTotalBalanceDue('000000002'),0)

How can my UPDATE statement be modified to so that the scalar-valued function returns only one row for each row that the UPDATE statement is updating?


Here is the code for the scalar-valued function:

ALTER FUNCTION [dbo].[fnGetAgentTotalBalanceDue]
(
-- Add the parameters for the function here
@TaxIdNumber char(9)
)
RETURNS money
AS
BEGIN
-- Declare the return variable here
DECLARE @Result money

-- Add the T-SQL statements to compute the return value here
SELECT @Result =
(SELECT a.previousBalanceDue +
SUM(i.newAmount) +
SUM(i.renewalAmount) -
SUM(i.refundAmount) -
SUM(i.newAmountRetained) -
SUM(i.renewalAmountRetained) -
SUM(i.refundAmountRetained) as AgentTotalBalanceDue
FROM Agent a JOIN Ismdata i ON a.taxIdNumber = i.taxIdNo WHERE a.taxIdNumber = @TaxIdNumber GROUP BY a.previousBalanceDue)

-- Return the result of the function
RETURN @Result

END

 

[jbenson001]

Somewhere in your inner select in the function, the select is returning more than one row. You will have to check the data and find out why.

 

[bborissov]

Run this in SSMS (or QA):

SELECT a.taxIdNumber,
       a.previousBalanceDue,
       a.previousBalanceDue +
         SUM(i.newAmount) +
         SUM(i.renewalAmount) -
         SUM(i.refundAmount) -
         SUM(i.newAmountRetained) -
         SUM(i.renewalAmountRetained) -
         SUM(i.refundAmountRetained) as AgentTotalBalanceDue
    FROM Agent a 
JOIN Ismdata i ON a.taxIdNumber = i.taxIdNo
GROUP BY a.taxIdNumber, a.previousBalanceDue
HAVING COUNT(*) > 1

and see for what taxIdNumber you have more than one previousBalanceDue.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[hrg]

Thanks for your reply.

No row in the result set from the scalar-valued function is a duplicate, they are all unique. The problem is that there is more than one row - I think we understand that to be crux of the problem.

Perhaps my understanding of the inner workings of SQL is at fault: I believed that the UPDATE statement would pass the [taxIdNumber] from the current row it was updating to the function as a parameter. The function would then return the value calculated by the function for that specific [taxIdNumber] - meaning that only one row and one column would be returned (as in the example where I provided a string literal as the parameter to the function).

It appears to me that this is not the case; that the scalar-valued function, as it executes within the UPDATE statement, is returning the results of all of [taxIdNumbers]'s for each row that it is updating, rather than for just the [taxIdNumber] assoicated with the specific row it is updating. Am I wrong to assume this?

To test this theory, I modified the UPDATE statement to limit the scope of the update to a single agent row as follows:

UPDATE Agent SET previousBalanceDue = (select isnull(dbo.fnGetAgentTotalBalanceDue(taxIdNumber),0) where taxidnumber='000000002')

I recevied the same error: Subquery returned more than one value.

 

[gmmastros]

try this:

UPDATE Agent 
SET    previousBalanceDue = Isnull(dbo.fnGetAgentTotalBalanceDue(taxIdNumber),0)
where  taxidnumber='000000002'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hrg]

Yes this works, but only for the one Agent (taxIdNumber). I need it work through all agents (taxIdNumber's) in the agent table, updating them with the value calculated in the scalar-valued function.

I supposed I could use the method you propose from user-interface, creating looping structure that sends the taxIdNumber to the SP as a parameter, but I'd rather the storped procedure (and associated function) were able to handle it.

This seems like a common thing to do, but I can't seem to figure it out. Thanks for your continued support.