Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Question Unanswered: Transact SQL -- how do I use "Case"?

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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)?

  3. #3
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Try this

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

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •