July 15, 2009 at 7:33 pm
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.
July 15, 2009 at 8:44 pm
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
July 16, 2009 at 6:07 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2009 at 11:27 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply