Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Correct Syntax for CASE WHEN statements SQL

Posted on 2013-06-19
5
Medium Priority
?
314 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

885 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