Correct Syntax for CASE WHEN statements SQL

Posted on 2013-06-19
Last Modified: 2013-07-24
I am trying the following case statements in a query but getting blanks
SELECT score,
			WHEN score>3 THEN 'Unnecessary'
			WHEN score is NULL  THEN 'Unnecessary'
			WHEN score='' THEN 'Unnecessary'
			AS NewScore
from tmptabl;

Open in new window

Is the above syntax right or is there another way to achieve this.

Question by:Mohit_t
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

eli411 earned 250 total points
ID: 39260531
Seems to be right!

Assisted Solution

thenerdynerd earned 250 total points
ID: 39260574
Agreed is correct what does the results grid show?  Nulls?  Maybe score doesn't equal any of those params?  Maybe in else instead of NULL place a string?  'Failed' or something?
LVL 48

Expert Comment

ID: 39261081
as you are testing score for = '' I suspect you score field isn't a number type

maybe try some tests with this (note, 'a' is considered > '3')

declare @score as varchar = 'a'

      , CASE
            WHEN @score > '3'
                  THEN 'Unnecessary'
            WHEN @score IS NULL OR @score = ''
                  THEN 'Unnecessary'
            ELSE @score --NULL
            END AS NewScore
--FROM tmptabl;

Author Comment

ID: 39261344
Hi PortletPaul,
I tried running your query but got error "Incorrect syntax near '@score'.". I tried only IS NULL but that also gives me the same results. I randomly get "Unnecessary" value in the table. I don't have any permissions on the mssql server except for select, what I am trying to achieve is run the query and get the result set from MSSql server and put the values in a mysql db. I successfully get all the values except for the transformations that I am doing in the above query.
Appreciate if experts can suggest where to look for some issues.

LVL 48

Expert Comment

ID: 39261372
are you familiar with @variables?
you have to include the declare line and all lines under it
the objective was to get you to test a few individual values by changing the @score

btw: am I correct - is the field [score] a varchar (i.e. not a number)?

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question