SQL Server CASE statement with mupltiple conditionals syntax

Question

I have a need to add a case statement into a select, but I cannot seem to get the syntax right, could anyone help?

SELECT  
    uuid = pnt.ID
    ,extras = (CASE (SELECT pnt.TypeID as [type], pnt.Source as source)
                  WHEN source = 7 THEN 'a'
                  WHEN source = 1 AND [type] = 0 THEN 'b'
                  WHEN source = 8 THEN 'c'
                  WHEN source = 2 AND [type] = 0 THEN 'd'
                  WHEN source = 3 AND [type] IN (5,6,7,8) THEN 'e'
                  ELSE NULL
                END)
FROM 
    Mydata as pnt

There are multiple problems, the select within the case is incorrect, the condition source = 7 is wrong, the combining conditions with an AND is wrong and the condition using IN is incorrect.

I used the answer to Multiple Criteria In Case Statement as a basis for the case statement.

I don't get this at all, you want two boolean columns as result? — jarlh, Feb 19 '15 at 11:12
the correct term for what you are discussing is `case expression` see: http://stackoverflow.com/questions/12436859/case-expression-vs-case-statement — Paul Maxwell, Feb 19 '15 at 11:28

score 3 · Answer 1 · answered Feb 19 '15 at 11:14

SELECT  
@uuid            = pnt.ID
,@extras         = (CASE WHEN source = 7 THEN
                                'a'
                            WHEN source = 1 AND [type] = 0 THEN
                                'b'
                            WHEN source = 8 THEN
                                'c'
                            WHEN source = 2 AND [type] =0 THEN
                                'd'
                            WHEN source = 3 AND [type] IN  (5,6,7,8) THEN
                                'e'
                            ELSE NULL
                        END
                        )
FROM 
    Mydata as pnt

Paul Maxwell · Answer 2 · 2015-02-19T11:26:57.333

SELECT
      PNT.ID AS UUID
    , CASE
            WHEN PNT.source = 7 THEN 'a'
            WHEN PNT.source = 1 AND PNT.[type] = 0 THEN 'b'
            WHEN PNT.source = 8 THEN 'c'
            WHEN PNT.source = 2 AND PNT.[type] = 0 THEN 'd'
            WHEN PNT.source = 3 AND PNT.[type] IN (5, 6, 7, 8) THEN 'e'
            ELSE NULL
      END AS EXTRAS
FROM Mydata AS PNT

As you are already working within the table Mydata a case expression has access to the values held in [source] and [type] without an added select.

I have put the column aliases at the end of each column definition, I believe this is more generally supported by databases than using = for that purpose.

score 2 · Accepted Answer · answered Feb 19 '15 at 12:30

There are two types of case statements:

A simple case statement that compares an expression to a set of simple expressions to return specific values.
A searched case statement that evaluates a set of Boolean expressions to return specific values.

https://msdn.microsoft.com/en-us/library/ms144841(v=sql.105).aspx

In your script your're "mixing" them, so that your script doesn't work. This could be a good solution:

SELECT
    pnt.ID
    ,CASE
        WHEN pnt.source = 7 THEN 'a'
        WHEN pnt.source = 1 AND pnt.TypeID = 0 THEN 'b'
        WHEN pnt.source = 8 THEN 'c'
        WHEN pnt.source = 2 AND pnt.TypeID = 0 THEN 'd'
        WHEN pnt.source = 3 AND pnt.TypeID IN (5, 6, 7, 8) THEN 'e'
        ELSE NULL
    END
FROM
    @Mydata AS pnt

Warning! If you need to populate single variables (uuid, extras) you have to be sure that your query's result will have only 1 record

score 1 · Answer 4 · answered Feb 19 '15 at 11:14

1

SELECT ID As uuid, CASE WHEN Source = 7 THEN
'a'
WHEN Source = 1 AND TypeID= 0 THEN
'b'
WHEN Source = 8 THEN
'c'
WHEN Source = 2 AND TypeID= 0 THEN
'd'
WHEN Source = 3 AND TypeID IN (5,6,7,8) THEN
'e'
ELSE
NULL
END 
AS extras FROM Mydata

answered Feb 19 '15 at 11:14

Imran Ali Khan

8,469
16
52
77

SQL Server CASE statement with mupltiple conditionals syntax

4 Answers4