case statement returns multiple rows

Question

I want to return multiple rows in case statement. is it possible? or is there any alternate way to do it?

select 
   case 
      when 3 = 1 
          then (select orderid from order_master where noOfInstallment = installmentPaid) 
      else 
          (select orderid from order_master where noOfInstallment <> installmentPaid) 
   END

Both sub queries returns multiple rows. Now above query showing following error.

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

score 2 · Answer 1 · answered Dec 29 '11 at 07:51

2

CASE in SQL Server is not a flow control statement (it's different than the switch statement in C#) - it's just used to return one of several possible values.

You need to use IF statements in T-SQL

IF 3 = 1
   select orderid from order_master where noOfInstallment = installmentPaid
ELSE
   select orderid from order_master where noOfInstallment <> installmentPaid

or something like this.

answered Dec 29 '11 at 07:51

marc_s

732,580
175
1,330
1,459

Actually i dont want to write multiple queries. I want use resultset of above query to another subquery. – Abhi Dec 29 '11 at 08:03
@Abhi: you cannot use `CASE` in this way - it can return **a single value** only. If you have subqueries that might return more than one value - you cannot use `CASE` to handle this – marc_s Dec 29 '11 at 08:27

CloudyMarble · Answer 2 · 2011-12-29T08:19:43.630

0

You are not using the case statement in a right way, please take alook at the documentation.

Can you manage with a query like this:

select orderid,  
   case 
      when (noOfInstallment = installmentPaid) then
        'equal'   
      else 
        'not equal'
   END
from order_master

edited Dec 29 '11 at 08:19

answered Dec 29 '11 at 08:05

CloudyMarble

36,908
70
97
130

My query is working fine when subquery returns only 1 row. but it fails when subquery returns multiple rows. Can you please give me exact way to write query? – Abhi Dec 29 '11 at 08:09

score 0 · Accepted Answer · answered Dec 31 '11 at 10:23

0

I got solution.

 SELECT o.* FROM Order_Master as o Where 
   (
      ((@paymentStatus = 0) AND (o.noOfInstallment <> o.installmentPaid))
      OR 
      ((@paymentStatus=1) AND (o.noOfInstallment = o.installmentPaid)) 
   )

answered Dec 31 '11 at 10:23

Abhi

1,963
7
27
33

case statement returns multiple rows

3 Answers3