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?

score 5 · Answer 1 · 2011-11-09T01:27:07.547

5

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!

edited Nov 09 '11 at 01:27

answered Nov 08 '11 at 14:01

Shark, I'm pretty sure you can alias with `[Alias] = CASE statement` though I prefer an alias after the `END` as you have. – Adam Wenger Nov 09 '11 at 01:23
@AdamWenger I did not know that. I'll have to test that out! Thanks for the correction, it's these little not-so-well-known pieces of information that make us better! Thanks again. – Nov 09 '11 at 01:25
@AdamWenger Sure enough, you are right. I modified my answer with the correction. Thanks! – Nov 09 '11 at 01:27
No problem. I re-learned it when I started answering more SO questions and I saw it in questions/answers from others. Did not think it was part of mssql, had to try it for myself! – Adam Wenger Nov 09 '11 at 01:30
@AdamWenger ha! Ain't that the truth. I guess "seeing is believing". ;) – Nov 09 '11 at 01:31

score 3 · Answer 2 · answered Nov 08 '11 at 14:02

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 as [ORDERS_ID]
FROM ORDERS;
GO

answered Nov 08 '11 at 14:02

Royi Namir

144,742
138
468
792

score 3 · Answer 3 · answered Nov 08 '11 at 14:03

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

score 2 · Answer 4 · answered Nov 08 '11 at 14:03

2

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

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

answered Nov 08 '11 at 14:03

Stuart Ainsworth

12,792
41
46

score 1 · Answer 5 · answered Nov 08 '11 at 14:02

1

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

answered Nov 08 '11 at 14:02

StevieG

8,639
23
31

T-SQL - Error on Case Statement

5 Answers5