OR is not supported with CASE Statement in SQL Server

Question

The OR operator in the WHEN clause of a CASE statement is not supported  How can I do this   CASE ebv db no      WHEN 22978 OR 23218 OR 23219 THEN  WECS 9500       ELSE  WECS 9520   END as wecs system

User · Answer

Try  CASE WHEN ebv db no IN  22978 23218 23219  THEN  WECS 9500  ELSE  WECS 9520  END

User · Answer

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 9500     ELSE  WECS 9520   END as wecs system    Otherwise  use   CASE     WHEN ebv db no IN  22978  23218  23219  THEN  WECS 9500     ELSE  WECS 9520   END as wecs system

User · Answer

You can use one of the expressions that WHEN has  but you cannot mix both of them    WHEN when expression  Is a simple expression to which input expression is compared when the simple CASE format is used  when expression is any valid expression  The data types of input expression and each when expression must be the same or must be an implicit conversion  WHEN Boolean expression  Is the Boolean expression evaluated when using the searched CASE format  Boolean expression is any valid Boolean expression    You could program   1       CASE ProductLine             WHEN  R  THEN  Road              WHEN  M  THEN  Mountain              WHEN  T  THEN  Touring              WHEN  S  THEN  Other sale items              ELSE  Not for sale    2       CASE             WHEN ListPrice    0 THEN  Mfg item - not for resale              WHEN ListPrice  lt  50 THEN  Under  50              WHEN ListPrice  gt   50 and ListPrice  lt  250 THEN  Under  250              WHEN ListPrice  gt   250 and ListPrice  lt  1000 THEN  Under  1000              ELSE  Over  1000            END   But in any case you can expect that the variable ranking is going to be compared in a boolean expression   See CASE  Transact-SQL   MSDN

User · Answer

Select s stock code s stock desc s stock desc ar  mc category name s sel price  case when s allow discount 0 then  Non Promotional Item  else  Prmotional  item  end  Promotion  From tbl stock s inner join tbl stock category c on s stock id c stock id inner join tbl category mc on c category id mc category id where mc category id 2 and s isSerialBased 0

User · Answer

CASE   WHEN ebv db no   22978 OR         ebv db no   23218 OR        ebv db no   23219   THEN  WECS 9500     ELSE  WECS 9520   END as wecs system

User · Answer

There are already a lot of answers with respect to CASE  I will explain when and how to use CASE   You can use CASE expressions anywhere in the SQL queries  CASE expressions can be used within the SELECT statement  WHERE clauses  Order by clause  HAVING clauses  Insert  UPDATE and DELETE statements   A CASE expression has the following two formats    Simple CASE expression  CASE expression WHEN expression1 THEN Result1 WHEN expression2 THEN Result2 ELSE ResultN END   This compares an expression to a set of simple expressions to find the result  This expression compares an expression to the expression in each WHEN clause for equivalency  If the expression within the WHEN clause is matched  the expression in the THEN clause will be returned   This is where the OP s question is falling  22978 OR 23218 OR 23219 will not get a value equal to the expression i e  ebv db no  That s why it is giving an error  The data types of input expression and each when expression must be the same or must be an implicit conversion  Searched CASE expressions  CASE WHEN Boolean expression1 THEN Result1 WHEN Boolean expression2 THEN Result2 ELSE ResultN END   This expression evaluates a set of boolean expressions to find the result  This expression allows comparison operators  and logical operators AND OR with in each Boolean expression    1 SELECT statement with CASE expressions  --Simple CASE expression   SELECT FirstName  State  CASE StateCode  WHEN  MP  THEN  Madhya Pradesh    WHEN  UP  THEN  Uttar Pradesh    WHEN  DL  THEN  Delhi    ELSE NULL   END   PayRate FROM dbo Customer  -- Searched CASE expression  SELECT FirstName State  CASE   WHEN StateCode    MP  THEN  Madhya Pradesh    WHEN StateCode    UP  THEN  Uttar Pradesh    WHEN StateCode    DL  THEN  Delhi    ELSE NULL   END   PayRate FROM dbo Customer   2 Update statement with CASE expression  -- Simple CASE expression   UPDATE Customer  SET StateCode   CASE StateCode  WHEN  MP  THEN  Madhya Pradesh    WHEN  UP  THEN  Uttar Pradesh    WHEN  DL  THEN  Delhi    ELSE NULL   END   -- Simple CASE expression   UPDATE Customer  SET StateCode   CASE   WHEN StateCode    MP  THEN  Madhya Pradesh    WHEN StateCode    UP  THEN  Uttar Pradesh    WHEN StateCode    DL  THEN  Delhi    ELSE NULL   END    3 ORDER BY clause with CASE expressions  -- Simple CASE expression   SELECT   FROM dbo Customer ORDER BY   CASE Gender WHEN  M  THEN FirstName END Desc   CASE Gender WHEN  F  THEN LastName END ASC  -- Searched CASE expression   SELECT   FROM dbo Customer ORDER BY   CASE WHEN Gender  M  THEN FirstName END Desc   CASE WHEN Gender  F  THEN LastName END ASC   4 Having Clause with CASE expression  -- Simple CASE expression   SELECT FirstName  StateCode Gender  Total MAX PayRate  FROM dbo Customer GROUP BY StateCode Gender FirstName HAVING  MAX CASE Gender WHEN  M    THEN PayRate   ELSE NULL END   gt  180 00  OR MAX CASE Gender WHEN  F    THEN PayRate   ELSE NULL END   gt  170 00   -- Searched CASE expression   SELECT FirstName  StateCode Gender  Total MAX PayRate  FROM dbo Customer GROUP BY StateCode Gender FirstName HAVING  MAX CASE WHEN Gender    M    THEN PayRate   ELSE NULL END   gt  180 00  OR MAX CASE WHEN Gender    F    THEN PayRate   ELSE NULL END   gt  170 00    Hope this use cases will help someone in future   Source

User · Answer

UPDATE table name    SET column name CASE  WHEN column name in   value1    value2            THEN  update value   WHEN column name in   value1    value2            THEN  update value   END   table name   The name of table on which you want to perform operation    column name   The name of Column Field of which value you want to set    update value   The value you want to set of column name

User · Answer

CASE WHEN ebv db no IN  22978  23218  23219  THEN  WECS 9500    ELSE  WECS 9520   END as wecs system

User · Answer

select id phno case gender when  G  then  M  when  L  then  F  else  No gender  end as gender  from contacts

User · Answer

CASE   WHEN ebv db no   22978 OR         ebv db no   23218 OR        ebv db no   23219   THEN  WECS 9500     ELSE  WECS 9520   END as wecs system

User · Answer

SELECT   Store Name    CASE Store Name     WHEN  Los Angeles  THEN Sales   2     WHEN  San Diego  THEN Sales   1 5     ELSE Sales     END AS  New Sales     Txn Date FROM Store Information

[sql] OR is not supported with CASE Statement in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to case

Examples related to case-when