Case in Statement with Multiple Values

CASE IN statement with multiple values

Yes. You need to use the "Searched" form rather than the "Simple" form of the CASE expression

SELECT CASE
         WHEN c.Number IN ( '1121231', '31242323' ) THEN 1
         WHEN c.Number IN ( '234523', '2342423' ) THEN 2
       END AS Test
FROM   tblClient c  

case statement for multiple values

You have a table with one row per ID and project. You want a result with one row per ID. This means you must aggregate your rows with GROUP BY id. In your query you don't, so project IN (150 ) AND project IN(151) refers to one row and one project only, which equals project = 150 OR project = 151 or simply project IN (150, 151). You must count matches instead (i.e. whether you have a match in the group), for which you'd use conditional aggregation (CASE inside the aggregation function).

Then, your order of boolean expressions is wrong. You opt for "project 150 only" before checking project 151. Thus you'll ever detect "both".

But yes, for two statuses in your select clause you need two CASE expresions.

SELECT 
  pd.id ,
  CASE 
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 1 THEN 1 END) > 0
     AND COUNT(CASE WHEN project = 151 AND opted_status = 1 THEN 1 END) > 0
      THEN 'BOTH_prj_y'
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 1 THEN 1 END) > 0
      THEN 'prj_150_y'
    WHEN COUNT(CASE WHEN project = 151 AND opted_status = 1 THEN 1 END) > 0
      THEN 'prj_151_y'
  END AS opt_in,
  CASE 
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 2 THEN 1 END) > 0
     AND COUNT(CASE WHEN project = 151 AND opted_status = 2 THEN 1 END) > 0
      THEN 'BOTH_prj_y'
    WHEN COUNT(CASE WHEN project = 150 AND opted_status = 2 THEN 1 END) > 0
      THEN 'prj_150_y'
    WHEN COUNT(CASE WHEN project = 151 AND opted_status = 2 THEN 1 END) > 0
      THEN 'prj_151_y'
  END AS opt_out
FROM proj_dept pd
GROUP BY pd.id
ORDER BY pd.id;

How to assign multiple values in CASE statement?

Functions destroy performance. But you could use a common-table-expression(cte):

with cte as
(
    Select IsNameInList1 = case when name in ('A', 'B') 
                           then 1 else 0 end,
           IsNameInList2 = case when name in ('C', 'D') 
                           then 1 else 0 end,
           t.*
    from table
)
select
    userid
  , case when IsNameInList1=1 then 'Apple'
         when IsNameInList2=1 then 'Pear'
    end as snack
  , case when IsNameInList1=1 then 'Milk'
         when IsNameInList2=1 then 'Cola'
    end as drink
from
    cte
;

On this way you have only one place to maintain.

If query performance doesn't matter and you want to use a scalar valued function like this:

CREATE FUNCTION [dbo].[IsNameInList1] 
(
    @name varchar(100)
)
RETURNS bit
AS
BEGIN
DECLARE @isNameInList bit

BEGIN
    SET @isNameInList =  
    CASE WHEN @name in ('A', 'B')
    THEN 1
    ELSE 0 
    END
END

 RETURN @isNameInList 
END

Then you can use it in your query in this way:

select
    userid
  , case when dbo.IsNameInList1(name) = 1 then 'Apple'
         when dbo.IsNameInList2(name) = 1 then 'Pear'
    end as snack
from
    table
;

But a more efficient approach would be to use a real table to store them.

CASE Statements with multiple values

This CASE statement is part of a SELECT, ORDER BY, or HAVING clause.

For this error, in conjuction with this code, you must also have a GROUP BY clause. You need to include B as part of that group by clause. Otherwise, B represents a whole range of possible values, some of which might or might not meet the = 'HH' condition; the code is ambiguous.

case statement in SQL, how to return multiple variables?

The basic way, unfortunately, is to repeat yourself.

SELECT
  CASE WHEN <condition 1> THEN <a1> WHEN <condition 2> THEN <a2> ELSE <a3> END,
  CASE WHEN <condition 1> THEN <b1> WHEN <condition 2> THEN <b2> ELSE <b3> END
FROM 
  <table> 

Fortunately, most RDBMS are clever enough to NOT have to evaluate the conditions multiple times. It's just redundant typing.

In MS SQL Server (2005+) you could possible use CROSS APPLY as an alternative to this. Though I have no idea how performant it is...

SELECT
  *
FROM
  <table>
CROSS APPLY
  (
   SELECT a1, b1 WHERE <condition 1>
   UNION ALL
   SELECT a2, b2 WHERE <condition 2>
   UNION ALL
   SELECT a3, b3 WHERE <condition 3>
  )
  AS case_proxy

The noticable downside here is that there is no ELSE equivalent and as all the conditions could all return values, they need to be framed such that only one can ever be true at a time.

EDIT

If Yuck's answer is changed to a UNION rather than JOIN approach, it becomes very similar to this. The main difference, however, being that this only scans the input data set once, rather than once per condition (100 times in your case).

EDIT

I've also noticed that you may mean that the values returned by the CASE statements are fixed. All records that match the same condition get the exact sames values in value1 and value2. This could be formed like this...

WITH
  checked_data AS
(
  SELECT
    CASE WHEN <condition1> THEN 1
         WHEN <condition2> THEN 2
         WHEN <condition3> THEN 3
         ...
         ELSE                   100
    END AS condition_id,
    *
  FROM
    <table>
)
,
  results (condition_id, value1, value2) AS
(
   SELECT 1, a1, b1
   UNION ALL
   SELECT 2, a2, b2
   UNION ALL
   SELECT 3, a3, b3
   UNION ALL
   ...
   SELECT 100, a100, b100
)
SELECT
  *
FROM
  checked_data
INNER JOIN
  results
    ON results.condition_id = checked_data.condition_id

Using Case Statement to match multiple values on single row

Group your areas query by emp_code. Or apply WHERE EXISTS construction. Here's the example.

SQL Case with multiple values

In Oracle, assuming statuid is never <= 0:

SELECT Name, CASE WHEN statusid < 4 THEN 'Alive'
                  WHEN statusid = 4 THEN 'Dying'
                  ELSE 'Dead' END AS some_alias
  FROM people

You can also use DECODE.

SQL CASE THEN statement where ID has multiple values

You can do conditional aggregation:

select 
    person_id,
    max(case when vehicle_type = 'TRUCK' then 'Y' else 'N' end) truck,
    max(case when vehicle_type = 'CAR' then 'Y' else 'N' end) car
from cars
group by person_id

Return multiple values for 'Then' clause in an SQL case expression

You can try below - use UNION

DEMO

    select * from
    (
    select a.fileid,contolnames,file1 as files
    from Files a inner join sampletable b on a.fileid=b.fileid
    union
    select a.fileid,contolnames,file2 as files 
    from Files a inner join sampletable b on  a.fileid=b.fileid
  )A order by fileid

OUTPUT:

fileid  contolnames files
1         abc   abc.pdf
1         abc   abcdef.pdf
2         ghf   ghf.pdf
2         ghf   ghfjkl.pdf
3         hjy   hjyui.pdf
3         hjy   hjy.pdf

---

Related Topics