T-SQL - Error on Case Statement

Question

USE NORTHWIND;
GO
SELECT SHIPCOUNTRY,[ORDERS_ID] = 
CASE ORDERID
WHEN ORDERID = 10300
THEN 'I AM FROM 10300'
WHEN ORDERID = 10400
THEN 'I AM FROM 10400'
WHEN ORDERID = 10500
THEN 'I AM FROM 10500'
ELSE 'I AM OUT OF RANGE'
END
FROM ORDERS;
GO

Error - Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '='.

If you have northwind Database in your sql server,you can execute this query.

I don't understand where the issue is.Can anyone help me to resolve this issue?

Answer 1

Try this:

USE NORTHWIND; 
GO 
SELECT SHIPCOUNTRY,  
CASE ORDERID 
WHEN 10300 
THEN 'I AM FROM 10300' 
WHEN 10400 
THEN 'I AM FROM 10400' 
WHEN 10500 
THEN 'I AM FROM 10500' 
ELSE 'I AM OUT OF RANGE' 
END as OrdersId
FROM ORDERS; 
GO 

When you are explicit in your initial CASE xxxx statement, you can't repeat the variable in your WHEN clauses. This would equally work as well:

USE NORTHWIND; 
GO 
SELECT SHIPCOUNTRY, 
CASE
WHEN ORDERID = 10300 
THEN 'I AM FROM 10300' 
WHEN ORDERID = 10400 
THEN 'I AM FROM 10400' 
WHEN ORDERID = 10500 
THEN 'I AM FROM 10500' 
ELSE 'I AM OUT OF RANGE' 
END as OrdersId
FROM ORDERS; 
GO 

You also can't assign aliases like that. Use the AS keyword after your CASE block.

EDIT: As per Adam Wenger's comment, you can indeed write aliases like [alias] = fieldname in your query. Thanks for the correction!

Answer 2

USE NORTHWIND;
GO
SELECT SHIPCOUNTRY,
CASE ORDERID
WHEN  10300
THEN 'I AM FROM 10300'
WHEN  10400
THEN 'I AM FROM 10400'
WHEN  10500
THEN 'I AM FROM 10500'
ELSE 'I AM OUT OF RANGE'
END as [ORDERS_ID]
FROM ORDERS;
GO

Answer 3

USE NORTHWIND;
GO
SELECT SHIPCOUNTRY,
CASE ORDERID 
  WHEN 10300
    THEN 'I AM FROM 10300'
  WHEN 10400
    THEN 'I AM FROM 10400'
  WHEN 10500
    THEN 'I AM FROM 10500'
  ELSE 'I AM OUT OF RANGE'
END ORDERS_ID
FROM ORDERS;
GO

Answer 4

CASE comes with two formats: Simple and Searched. You're mixing them.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Answer 5

The case construct can have two different forms:

case n
  when n then n
  when n then n
end

and:

case
  when n = n then n
  when n = n then n
end

You are mixing them. Use:

SELECT SHIPCOUNTRY,[ORDERS_ID] = 
  CASE ORDERID
    WHEN 10300 THEN 'I AM FROM 10300'
    WHEN 10400 THEN 'I AM FROM 10400'
    WHEN 10500 THEN 'I AM FROM 10500'
    ELSE 'I AM OUT OF RANGE'
  END
FROM ORDERS