SQL - Case Statement

SQL 2008 / 2005

I have SQL Query Syntax issue on Case Statement - I am looking for some help. It's a simple SQL Syntax issue on Case Statement. I have commented that scenario in my开发者_StackOverflow中文版 Query which i have attached .. Please suggest to go forward..!

select   
    stuff(Dr.DrugNDCNbr,
          case when Dr.DrugNDCType in (50, 56) then 1 
               when Dr.DrugNDCType in (51, 57) then 6    
               when Dr.DrugNDCType = 52 then 10     
               -- when Dr.DrugNDCType = 49 then -- DO NOTHING .. GIVE THE ACTUAL VALUE
          end, 0, '0')   
    from drug Dr
    where Dr.drugnbrkey = 6284

---

It's a nested CASE expression you need

select   
    case
         when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr
         else
               stuff   (   Dr.DrugNDCNbr,
               case 
                       when Dr.DrugNDCType in (50, 56) then 1 
                       when Dr.DrugNDCType in (51, 57) then 6    
                       when Dr.DrugNDCType = 52 then 10     
               end, 0, '0')
    end

 from drug Dr
where Dr.drugnbrkey = 6284