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.

wilsonsfamily

SSC Enthusiast

Points: 140
More actions
June 7, 2011 at 12:52 pm

#99307

DECLARE @pushMaxCustomerRank int;
SET @pushMaxCustomerRank = (select NumericValue from ClientSettings where Setting like 'PushMaxCustomerRank')
SELECT
(SELECT Rank
from Distributor
WHERE Rank <= (@pushMaxCustomerRank)) AS 'Rank <= ',
(SELECT Rank from Distributor
WHERE Rank > (@pushMaxCustomerRank)) AS 'Rank >'
Not sure how to fix this.
I need it to display two columns one with Customers and one with Distributors. But it is giving me the above mentioned error. Anyone able to assist?
Thanks in advance.

Viewing 7 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Without knowing your data its going to be hard, but the error is pretty clear, when you are using a sub-query it needs to return a single value. Until you get your query to return a single value it will fail.

Sorry I can't be more help.

CEWII

wilsonsfamily SSC Enthusiast Points: 140 More actions · Answer 2

How can I modify it? I need to run both queries.

I need to run this as the number can be different for each client.

DECLARE @pushMaxCustomerRank int;

SET @pushMaxCustomerRank =

(select NumericValue from ClientSettings where Setting = 'PushMaxCustomerRank')

After that runs, I need to run something similar to the following

SELECT Rank

from Distributor

WHERE Rank <= (@pushMaxCustomerRank)

This will return as many distributors who have a rank of @pushMaxCustomerRank or less.

So

Rank

0

I then need to run this:

SELECT Rank from Distributor

WHERE Rank > (@pushMaxCustomerRank)

Which return any distributors with ranks over @pushMaxCustomerRank

Rank

1

2

3

2

1

2

3

2

3

4

3

2

I hope that helps?

Edit: I need this to be in one report as I will be building a chart for it once it is done.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Does this return more than one row in some/all cases?

select NumericValue from ClientSettings where Setting like 'PushMaxCustomerRank'

If so, you'll get that error message, because you really haven't told SQL Server which row to pick, and it can only pick one of them.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

So this is for a report? Do you have one report that generates the data for all Clients?

I think I understand your problem a bit more but I need to understand your report more before I can give much guidance.

CEWII

wilsonsfamily SSC Enthusiast Points: 140 More actions · Answer 5

GSquared (6/7/2011)
Does this return more than one row in some/all cases?
select NumericValue from ClientSettings where Setting like 'PushMaxCustomerRank'
If so, you'll get that error message, because you really haven't told SQL Server which row to pick, and it can only pick one of them.

That line of code will always return one row.

wilsonsfamily SSC Enthusiast Points: 140 More actions · Answer 6

Elliott Whitlow (6/7/2011)
So this is for a report? Do you have one report that generates the data for all Clients?
I think I understand your problem a bit more but I need to understand your report more before I can give much guidance.
CEWII

I was able to work it out with a co-worker. Here is what we came up with.

DECLARE @pushMaxCustomerRank int;

SET @pushMaxCustomerRank = (select NumericValue from ClientSettings where Setting = 'PushMaxCustomerRank')

SELECT CONVERT(CHAR(4), CreatedDate, 100) + CONVERT(CHAR(4), CreatedDate, 120) AS 'Month'

,CAST(YEAR(CreatedDate) AS varchar(4)) + '-' + CAST(Month(CreatedDate) AS varchar(2)) AS 'NumericMonth'

,SUM((CASE WHEN RankID > @pushMaxCustomerRank THEN 1 ELSE 0 END)) AS 'Distrubutor'

,SUM((CASE WHEN RankID <= @pushMaxCustomerRank THEN 1 ELSE 0 END)) as 'Customer'

FROM DistributorDetail

WHERE CreatedDate > DATEADD(m, -5, GETDATE())

--GROUP BY CONVERT(CHAR(4), CreatedDate, 100) + CONVERT(CHAR(4), CreatedDate, 120)

GROUP BY CAST(YEAR(CreatedDate) AS varchar(4)) + '-' + CAST(Month(CreatedDate) AS varchar(2)), CONVERT(CHAR(4), CreatedDate, 100) + CONVERT(CHAR(4), CreatedDate, 120)

ORDER BY CAST(YEAR(CreatedDate) AS varchar(4)) + '-' + CAST(Month(CreatedDate) AS varchar(2))

There is extra stuff then I requested to separate it out by a 6 month period. This was a pain, and I now know how much SQL I need to learn still. Thanks all for helping out!

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

Cookies on SQLServerCentral