CODEWITHSUNDEEP
sqlsql-server
Nate Pet
Nate Pet

Reputation: 46222

sql server subquery with case

I have the following sql:

    select * from 
    ( 
         select p.ID, 
         Received = (select Rec from Exp   
                     where EstAmt = (select MAX(ex.EstAmt) from Exp ex 
                                     where ex.Prot = p.ID and EstAmt > 0) 
                    )    

   From Prot  
   ) subsel 
   where Received = 1

I want to do a case on the Received so if it is 1 then have it say 'Yes' else have it say 'No'. I know how to do a case but not in this situation as I have Received which is a subquery.

I tried the following but did not work

     select * from 
     ( 
       select p.ID, 

       case Received = (select Rec from Exp   
                        where EstAmt = (select MAX(ex.EstAmt) from Exp ex 
                                        where ex.Prot = p.ID and EstAmt > 0) 
                  )    
      when 1 then 'Yes'
      else 'No'
      end Received
      From Prot  
     ) subsel 
    where Received = 1

Upvotes: 1

Views: 7291

Answers (3)

regularmike
regularmike

Reputation: 1167

Instead of 

case Received = (select Rec from Exp   
                        where EstAmt = (select MAX(ex.EstAmt) from Exp ex 
                                        where ex.Prot = p.ID and EstAmt > 0) 
                  )

try:

case (select Rec from Exp   
                            where EstAmt = (select MAX(ex.EstAmt) from Exp ex 
                                            where ex.Prot = p.ID and EstAmt > 0) 
                      )

Other answers have important points too, but you can't assign the value of a subquery to Received, just use a single value after "case" and make sure the subquery does not return multiple values.

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Just like in the filter, get this case outside the sub query:

    select subsel.ID,
    case subsel.Received 
      when 1 then 'Yes'
      else 'No'
    end Received 
    from 
    ( 
         select p.ID, 
         Received = (select Rec from Exp   
                     where EstAmt = (select MAX(ex.EstAmt) from Exp ex 
                                     where ex.Prot = p.ID and EstAmt > 0) 
                    )    

   From Prot  
   ) subsel 
   where Received = 1

Upvotes: 0

Curtis
Curtis

Reputation: 103348

I think you need to remove 'Received' just after 'end'. Ie. change:

when 1 then 'Yes'
else 'No'
end Received

to

when 1 then 'Yes'
else 'No'
end

Upvotes: 0

Related Questions