Transact SQL -- how do I use "Case"?

thele · #1 (**permalink**) 12-08-05, 18:03

Hello, I am having some transact SQL problems.

ALLOWED:

Code:

	Column1, 
	CASE FOO
		WHEN 1 THEN 'Hello'
		ELSE 'Goodbye'
		END as MyValue,
	Column2

NOT ALLOWED:

Code:

	Column1, 
	CASE FOO
		WHEN > 1 THEN 'Hello'
		ELSE 'Goodbye'
		END as MyValue,
	Column2

The problem seems to be the > symbol. Apparantly the case statement is only for equality checks, and nothing else.

So, what can I do to fix this? I have these kinds of comparisons all over.

~Le

Littlefoot · #2 (**permalink**) 12-09-05, 01:21

Did you check the documentation about use of the CASE in Transact SQL? Oracle would allow this:

Code:

SELECT empno, ename,
       CASE
          WHEN sal > 1500
             THEN 'Salary > $1500'
          ELSE 'Salary <= $1500'
       END
  FROM employees;

Can you use it (try, before reading the manual)?

acg_ray · #3 (**permalink**) 12-21-05, 08:46

Column1,
CASE
WHEN FOO > 1 THEN 'Hello'
ELSE 'Goodbye'
END as MyValue,
Column2

Peter.Vanroose · #4 (**permalink**) 12-22-05, 03:18

This is not TransactSQL-specific, but holds for all SQL implementations:
The expression between the words WHEN and THEN must be a full valid expression,
either (as in your first case) in one of the SQL datatypes, or as a full logical expression
(like "FOO > 1" or "FOO IS NOT NULL" or "FOO IN (1,2,3)" etc.)

Internet Services

Web Development

Digital Marketing

Consumer Tech