Select Case syntax

Question

Hello SQL Server Expert,

The below code works, however, if I change it to > 130 I get the following error message.

Incorrect syntax near '>'.

SELECT Case EmployeeNumber WHEN 130 THEN 'Big' 
			WHEN 200 THEN 'Very Big'
			ELSE 'Small' END  
			FROM dbo.tblEmployee

If I use the long form method it works. I am curious why the > syntax does not work in the short form method.

SELECT CASE WHEN EmployeeNumber > 130 THEN 'Big' 
				WHEN EmployeeNumber > 200 THEN 'Very Big'
				ELSE 'Small' END  
				FROM dbo.tblEmployee

Thank you

Answer 1

Because it is not permitted.

The normal form of CASE is

CASE WHEN condition-1 THEN value-1
     WHEN condition-2 THEN value-2
     ...
     ELSE 
END

But there are a couple of shortcuts permitted. One is COALESCE(value-1, value-2, ..., value-N) which looks like a function, but in fact is a shortcut for

CASE WHEN value-1 IS NOT NULL THEN value-1
     WHEN value-2 IS NOT NULL THEN value-2
     ...
    ELSE value
END

There is also a shortcut for this type of expression:

CASE WHEN col = par-1 THEN value-1
     WHEN col = par-2 THEN value-2
     WHEN col = par-3 THEN value-3
     ...
     ELSE value-N
END

That is, the same column or variable is compared with the = operator to different values. For this type of CASE expression, you can use the shortcut in your first example. But it is exactly under these condition. As soon as something creeps in which deviates from this pattern, you cannot use this shortcut.

Answer 2

Hi @Peter Weinwurm

Because SQL Server does not recognize such syntactic forms.

When you use a form like 'Case EmployeeNumber WHEN 130 THEN 'Big'', it defaults to output Big when the EmployeeNumber is 130.

But when you want to put the '>' symbol in front of 130, the system can't recognize such a form. You can try, replace '>' with '=', '<' will get an error.

So you have to abandon this simple form and use the long form.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.