asked on

Correct Syntax for CASE WHEN statements SQL

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,

ASKER CERTIFIED SOLUTION

eli411

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

thenerdynerd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

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;

Mohit_t

ASKER

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,

PortletPaul

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)?