case when then IN

multiple case when sql
sql case when multiple values
nested case statement in sql
case statement with multiple conditions in sql server
case in where clause
sql case statement in where clause multiple values
case statement in sql oracle
sql case when null

below code does not work, please advise.

SELECT *
 FROM TBL_REASON_MAP A
WHERE case when V_ID = 3 then 'A' else to_char(a.type_id) end IN
      case when V_ID = 3 then 'A' else '(2,3)' end;

below is OK:

SELECT *
  FROM TBL_REASON_MAP A
 WHERE case when  V_ID = 3 then 'A' else to_char(a.type_id) end IN
       case when  V_ID = 3 then 'A' else '2' end;

note: V_ID is a variable to be passed a value in plsql block. If V_ID = 3 then it will return all data available, but if it's not it will return data with a.type_id in (2,3) only.

e.g. V_ID = 4

enter image description here

Presumably a.type_id contains values like 2, 3, 4 etc. The first case() returns a string of (2,3). It's a single value so the IN becomes an equality test. 2 does not equal '(2,3). Therefore the first code does not work. Whereas the second case() returns a single value 2 which can be matched to a value in a.type_id.


"anyway if V_ID = 3 then it will return all data available, but if its not, it will return data with a.type_id in (2,3)"

As other people have commented this is easy to implement with regular Boolean operations without the need for case() at all:

SELECT *
 FROM TBL_REASON_MAP A
WHERE V_ID = 3 
or (V_ID != 3 
    and a.type_id  IN (2,3));

OR is not supported with CASE Statement in SQL Server, That format requires you to use either: CASE ebv.db_no WHEN 22978 THEN '​WECS 9500' WHEN 23218 THEN 'WECS 9500' WHEN 23219 THEN 'WECS  It might be easier to read when written out in longhand using the 'simple case' e.g. CASE DeviceID WHEN '7 ' THEN '01' WHEN '10 ' THEN '01' WHEN '62 ' THEN '01' WHEN '58 ' THEN '01' WHEN '60 ' THEN '01' WHEN '46 ' THEN '01' WHEN '48 ' THEN '01' WHEN '50 ' THEN '01' WHEN '137' THEN '01' WHEN '139' THEN '01' WHEN '142' THEN '01' WHEN '143' THEN '01' WHEN '164' THEN '01' WHEN '8 ' THEN '02' WHEN

So far the solution Ive got is below:

Declare

    V_ID number;

begin

    V_ID := 4;

    SELECT *
      FROM TBL_REASON_MAP A
     WHERE case when V_ID = 3 then 'A' else to_char(a.type_id) end >=
           case when V_ID = 3 then 'A' else '2' end
       and case when V_ID = 3 then 'A' else to_char(a.type_id) end !=
           case when V_ID = 3 then 'A' else '0' end;

END;

SQL CASE, You can check each row to see whether year meets the condition year = 'SR' and then see the result in the column generated using the CASE statement. But what​  # case_when() evaluates all RHS expressions, and then constructs its # result by extracting the selected (via the LHS expressions) parts. # In particular NaN are produced in this case: y <-seq (-2, 2, by =.5) case_when ( y >= 0 ~ sqrt (y), TRUE ~ y) #>

The simplest logic is:

WHERE ( V_ID = 3 OR a.type_id IN (2, 3) );

There is no need for a CASE expression.

CASE statement in SQL, It can be used in Insert statement as well. In this article, we would explore CASE statement and its various use cases. Suppose you have a table  SELECT status, CASE status WHEN 'a1' THEN 'Active' WHEN 'a2' THEN 'Active' WHEN 'a3' THEN 'Active' WHEN 'i' THEN 'Inactive' WHEN 't' THEN 'Terminated' END AS StatusText FROM stage.tst Is there any other way of doing this where I don't need to write When expression 3 times for Active Status and the entire active status can be checked in one

SQL CASE Statement - Simple and Searched Forms, If there is no ELSE in the CASE statement, then it returns NULL. The ELSE clause is a great way to catch bad or unexpected data values, and return a result  The CASE statement goes through conditions and return a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it will return the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL. Syntax

SQL Server: CASE Statement, In SQL Server (Transact-SQL), the CASE statement has the functionality of an IF-​THEN-ELSE statement. You can use the CASE statement within a SQL  case ColumnName when 'A' then 'Apple' when 'B' then 'Banana' end ColumnName, case ColumnName when 'A' then '1' when 'B' then '2' end ExtraColumn, There is a gotcha here. If you use ColumnName in your where clause, you might not like the results because you used it as an alias.

CASE (Transact-SQL), Aggregate expressions that appear in WHEN arguments to a CASE expression are evaluated first, then provided to the CASE expression. For  A general vectorised if. This function allows you to vectorise multiple if and else if statements. It is an R equivalent of the SQL CASE WHEN statement.

Comments
  • please update your question according to this - stackoverflow.com/help/how-to-ask
  • It's generally better to use AND/OR constructions instead of case expressions in the WHERE clause.
  • Boolean are better for condition where clause not case statement.. Please show sample data and your expected output..
  • A case expression's different return types must be compatible. 'A' and (2,3) aren't.
  • Add some sample table data and the expected result - all as formatted text, not images.
  • Hi Thanks for the answer, Ive attached the sample data. this sample query will run on a plsql block(apologies, i should have provided a plsql block, in a hurry Im currently testing it in SQL.), anyway if V_ID = 3 then it will return all data available, but if its not, it will return data with a.type_id in (2,3) only.
  • "in a hurry Im currently testing it in SQL" On StackOverflow we get answers quicker if we slow down and ask a clear and complete answer ;-)
  • thanks I'll keep that in mind. :) actually ive got an idea on your first answer, that case when function, the IN becomes an equality test, so I was able to formulate my answer.
  • I updated my case-less solution to incorporate V_ID. It's concise and comprehensible.