Sql Case With In Statement as a condition

Back to Forums

roy mainfeld

Solved

Question

SQL

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.

06 Mar 2022

Maria da Graça Peixoto

Solution

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

See solution in context

07 Mar 2022

Shahaji Nangare

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

07 Mar 2022

Maria da Graça Peixoto

Solution

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

07 Mar 2022

Matheus jesus

This post me helped very!
Thanks dude!

03 Mar 2023

FRANK JOHNNY GANGUILHET

Thanks for the post and the solucion!

04 Mar 2023

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

See the full guidelines