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

Question

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

micbra

Old Hand

Points: 335
More actions
July 15, 2009 at 7:33 pm

#206991

Full Error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have the following code and it is returning the error (shown in the Subject line (truncated) and above)...
DECLARE @tmpTotalPrice AS MONEY
SELECT
@tmpTotalPrice = a.rateprice + a.featuresprice
FROM
accountinfo a
SELECT
a.location AS Location, a.name AS Name,
a.wirelessnum AS 'Wireless No', a.rateplanmins AS 'Rate Plan Minutes',
a.rateprice AS 'Rate Price', a.featuresprice AS 'Features Price', @tmpTotalPrice AS 'Total Price'
FROM
accountinfo a
GROUP BY a.location, a.name, a.wirelessnum, a.rateplanmins, a.rateprice, a.featuresprice
ORDER BY a.location, a.name, a.wirelessnum
GO
I am trying to add the rateprice and featureprice fields to the tmpTotalPrice variable but it is returning the same value for each row (probably the last row).
My research has shown this is a problem when working with multiple rows but I haven't found a solution.
Any help would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

this is most likely the error:

if the table accountinfo has two or more rows, then you'd get the error you describe...maybe you need a WHERE statement to limit the rate + feature to a single row? featureid=2 or something??

SELECT

@tmpTotalPrice = a.rateprice + a.featuresprice

FROM

accountinfo a

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

The variable can only be loaded once. So it's going to be set a single time and then when you reference it in the second select statement it will only have one value. It looks like you're trying to define the variable as a function. It doesn't work like that.

Instead, either generate the values on the fly, adding the two together, or you'll need to eliminate the variable declaration and change it to a sub-select that you join to within the main select statement.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

manish.singh-1128425 SSC-Addicted Points: 402 More actions · Answer 3

Hi,

Use the following querry

SELECT

a.location AS Location, a.name AS Name,

a.wirelessnum AS 'Wireless No', a.rateplanmins AS 'Rate Plan Minutes',

a.rateprice AS 'Rate Price', a.featuresprice AS 'Features Price',

a.rateprice + a.featuresprice AS 'Total Price'

FROM

accountinfo a

GROUP BY a.location, a.name, a.wirelessnum, a.rateplanmins, a.rateprice, a.featuresprice

ORDER BY a.location, a.name, a.wirelessnum

GO