Click here to Skip to main content
14,326,246 members
Rate this:
Please Sign up or sign in to vote.
See more:
this query return this message

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

What I have tried:

SELECT (a.NumUnit*(SELECT  sp.PriceUnitSale
FROM    SalePrice sp
        INNER JOIN
            SELECT  ProNo, MAX(PriceLastDate) max_date
            FROM    SalePrice
            GROUP   BY ProNo
        ) spt ON  sp.ProNo =a.ProNo AND sp.PriceLastDate = spt.max_date))Amount

FROM    StoreBalance a
        INNER JOIN
            SELECT  ProNo, MAX(BalanceDate) max_date,MAX(BalanceNo) max_No
            FROM    StoreBalance
            GROUP   BY ProNo
        ) b ON  a.ProNo =b.ProNo AND
                a.BalanceDate = b.max_date and  a.BalanceNo = b.max_No and year(BalanceDate)='2019'
Updated 1-Oct-19 21:59pm
Jörgen Andersson 2-Oct-19 5:54am
I bet you're having duplicate rows with PriceLastDate = MAX(PriceLastDate)
Rate this:
Please Sign up or sign in to vote.

Solution 1

The error means exactly what it says: your subquery returned more than one value.
Your code is effectively
SELECT a_column * (a_subquery) Amount FROM Mytable
And a_subquery return more than one value - which means you are asking SQL to multiply NumUnit by several values and it won't do that.

You need to look at your subquery closely - and I'd suggest running it on it's own to see what it does return - and sort it out so that it does return just one value.

Unfortunately, we can't do that: we don't have access to your DB, and we have no idea what you are trying to get NumUnit multiplied by!
Maciej Los 2-Oct-19 3:57am
Rate this:
Please Sign up or sign in to vote.

Solution 2

OriginalGriff is right.
In addition i'd strongly recommend to read this interesting article: Finding the reason for the error “Subquery returned more than 1 value” | SQL Studies[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100