case statement

Question

s
select case AcctType

when in (43,41,47,85,80,81,66,44) then 'AOP'
when in (67,18,55,13,21,88,46,14,69,11,20,52,90,42,61,58,74,83,5,37,76,22,15,25,35,91,19,56) then 'Others'
when in (32,84,60,24,12) then 'Corporate'
when in (50,30,4,70,87,82) then 'Trust'
when in (8) then 'HUF'
when in (53) then 'NPO'
when in (71) then 'PEP'
else end from D009022

and the error is

Incorrect syntax near the keyword 'in'.

what wrong I am doing here?

Answer 1

Try another form:

select case 
when AcctType in (43,41,47,85,80,81,66,44) then 'AOP'
when AcctType in (67,18,55, . . .) then 'Others'
. . .

To simplify this, maybe you can replace it with a join with special table.

Answer 2

To simplify this, maybe you can replace it with a join with special table.

HOW Sir?
Please help

Answer 3

hi all
thanks for your response
how do I close this thread?

Answer 4

Hi @Hemant Karnik ，

Welcome to the microsoft TSQL Q&A forum!

Your question is related to tsql, so my colleague helped you add the tsql tag.

Your problem is that you are not familiar with the syntax of the CASE WHEN statement.Please refer to:

    select   
    case when AcctType in (43,41,47,85,80,81,66,44) then 'AOP'  
         when AcctType in (67,18,55,13,21,88,46,14,69,11,20,52,90,42,61,58,74,83,5,37,76,22,15,25,35,91,19,56) then 'Others'  
         when AcctType in (32,84,60,24,12) then 'Corporate'  
         when AcctType in (50,30,4,70,87,82) then 'Trust'  
         when AcctType in (8) then 'HUF'  
         when AcctType in (53) then 'NPO'  
         when AcctType in (71) then 'PEP'else null end   
    from D009022  

There is also an error near your else statement, you can directly omit the ELSE sentence (in this case, the default ELSE NULL), or write the result you want after the ELSE. But you cannot write ELSE END.

Please refer to the two ways of writing CASE WHEN:

    case sex   
       when 1 then 'male'  
       when 2 then 'female'  
       else 'null' end  
      
    case  
    when sex =1 then 'male'  
    when sex = 2 then 'female'  
    else 'null' end  

For more details, please refer to:
CASE (Transact-SQL)

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.