Sql Case With In Statement as a condition
Question

Hello,

I am trying to figure out a way to write a case statement in sql with IN clause as a condition.

What I am basically trying to do is the following, I get a list of text as input( I build it with the sanitization module).

If the list contains the string"0"  I want to return true for all records, else I want to check if the specific record id (group id) is in the list and if so return true for the record.

Here is the full query:

This is the problematic row(the rest works perfectly fine):


The error:

Am I using the IN function correctly with the case statement? Does anybody have a better solution for my problem?

Thank you very much in advance, Roy.

HI!

Since you are in the "where" clause, why not forget the "Case" and do something like :

 ( (  (N'0' in (@ProjectTypeList))  or ( {ProjectsAnalitics}.[GroupId] in (@ProjectTypeList)) )

If you really want to use the Case , try that line in another SQL just to see if it works. 

Hope this helps.

Graça

There are below ways if you want to use the case statement 

1. Exact match 

SELECT * from {Messages} 
where  
case 
    when {Messages}.[message_tc] = 'ERROR' then 1 
    when {Messages}.[message_tc] = 'INFO' then 1 
    else 0 
END=1

2. LIKE 

SELECT * from {Messages} 
where  
case 
    when {Messages}.[message_tc] LIKE '%ERROR%' then 1 
    when {Messages}.[message_tc] LIKE '%INFO%' then 1 
    else 0 
END=1

Regards,

Shahaji

HI!

Since you are in the "where" clause, why not forget the "Case" and do something like :

 ( (  (N'0' in (@ProjectTypeList))  or ( {ProjectsAnalitics}.[GroupId] in (@ProjectTypeList)) )

If you really want to use the Case , try that line in another SQL just to see if it works. 

Hope this helps.

Graça

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.