case in Sql doesnt run as i expect-CodePudding

is it wrong if i expect that SQL understands that it should check every single rows to see if S.PersonID is null?

select OrderQty as 'OrderQty', 
case  
    when (select S.PersonID from Sales.Customer as S) is null then 1 
    else 2 
end as 'CaseResult' 
from sales.SalesOrderDetail

error: Subquery returned more than 1 value.

if it is wrong then why it understands that it should check every rows one by one in the example below?

select Name,
case ProductCategoryId
   when 1 then 'red'
   when 2 then 'black'
   else 'white
end as 'categoryname'
from production.productcategory

How can i write the first code so that it works like the second code?

CodePudding user response：

You can't use subqueries inside CASE Also, you are not doing a join between SalesOrderDetail and Customer

select OrderQty as 'OrderQty', 
case  
    when s.Person is null then 1 
    else 2 
end as 'CaseResult' 
from sales.SalesOrderDetail sod
left outer join sales.Customer s on sod.PersonId = s.PersonId

CodePudding user response：

You need a correlated subquery, to get it running correctly

CREATE TABLe Customer(PersonID Int)

INSERT INTO Customer VALUEs (1)

CREATE tABLe SalesOrderDetail ( OrderQty int, CustomerID int)

INSERT INTO SalesOrderDetail VALUES(2,1),(3,2)

Records: 2  Duplicates: 0  Warnings: 0

select OrderQty as 'OrderQty', 
case  
    when (select C.PersonID from Customer as C WHERE C.PersonID = S.CustomerID ) is null then 1 
    else 2 
end as 'CaseResult' 
from SalesOrderDetail S

OrderQty	CaseResult
2	2
3	1

fiddle

If you have a query that has multiple rows as resultset you need to add a LIMIT to the subquery

CREATE TABLe Customer(PersonID Int)

INSERT INTO Customer VALUEs (1)

CREATE tABLe SalesOrderDetail ( OrderQty int, CustomerID int)

INSERT INTO SalesOrderDetail VALUES(2,1),(3,2)

Records: 2  Duplicates: 0  Warnings: 0

select OrderQty as 'OrderQty', 
case  
    when (select C.PersonID from Customer as C WHERE C.PersonID = S.CustomerID ORDER BY C.PersonID LIMIT 1) is null then 1 
    else 2 
end as 'CaseResult' 
from SalesOrderDetail S

OrderQty	CaseResult
2	2
3	1

fiddle