Solved

Correct Syntax for CASE WHEN statements SQL

Posted on 2013-06-19
5
306 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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 …

713 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