Solved

Correct Syntax for CASE WHEN statements SQL

Posted on 2013-06-19
5
304 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 250 total points
ID: 39260531
Seems to be right!
0
 
LVL 1

Assisted Solution

by:thenerdynerd
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?
0
 
LVL 48

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 48

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now