CODEWITHSUNDEEP
sqlsql-serversql-server-2012
MikeW
MikeW

Reputation: 1620

SQL Server CASE statement with mupltiple conditionals syntax

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.

Upvotes: 2

Views: 151

Answers (4)

Matteo Avesani
Matteo Avesani

Reputation: 86

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

Upvotes: 2

Paul Maxwell
Paul Maxwell

Reputation: 35603

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.

Upvotes: 2

I A Khan
I A Khan

Reputation: 8859

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

Upvotes: 1

Sorrel Vesper
Sorrel Vesper

Reputation: 414

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

Upvotes: 3

Related Questions