Question 21274 in Mysql Question posted in
The official MySQL documentation can be found here.

case in Sql doesnt run as i expect – Mysql

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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search