Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Correct Syntax for CASE WHEN statements SQL

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

Open in new window

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

Thanks,
0
Comment
Question by:Mohit_t
5 Comments
 
LVL 2

Accepted Solution

by:
eli411 earned 750 total points
ID: 39260531
Seems to be right!
0
 
LVL 1

Assisted Solution

by:thenerdynerd
thenerdynerd earned 750 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?
0
 
LVL 49

Expert Comment

by:PortletPaul
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'

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

Author Comment

by:Mohit_t
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.

Thanks,
0
 
LVL 49

Expert Comment

by:PortletPaul
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)?
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

571 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