using > in case statement causes syntax error

Question

Using the below code gives me a incorrect syntax near '>' the data in the Long field looks like this:

Review Body 3,
Review body 6,
Aprentice,

I've been going around in cirlces for hours traying to sort this, is it becasue I'm using a text function (Right) in a calc.

SELECT TOP 1000 [Id]
      ,[Short]
      ,[Long]
      ,CASE ISNUMERIC(RIGHT( grade.Long , 1 )) WHEN  cast(RIGHT( shiftname.Long , 1 )as int) > 4  THEN 'Qualified' ELSE 'Unqualified' END  as Grade

  FROM [RosterPro_Live].[dbo].[lookup_PostGrade] as grade

`ISNUMERIC`returns either 1 or 0, so i dont understand the part after `WHEN` — Tim Schmelter, Jun 03 '15 at 11:05
You're mixing CASE syntax variations. Either you have `CASE expression WHEN value THEN ... WHEN othervalue THEN ...` **OR** `CASE WHEN expression THEN ... WHEN otherexpression THEN ...`. You can't both provide an expression after CASE and expressions after WHEN (unless those expressions after WHEN result in values corresponding to the expression after CASE). — Lasse V. Karlsen, Jun 03 '15 at 11:09
So, you check whether the last character of `grade.Long` can be converted to any numeric type, and then attempt to `cast` a *different* column's right most character to an `int`? Also, `ISNUMERIC` is rarely, if ever, the correct solution to any problem. — Damien_The_Unbeliever, Jun 03 '15 at 12:17

score 2 · Answer 1 · edited May 23 '17 at 12:14

Syntactically, as L.V. Karlsen indicates, this question overlaps with SQL Server CASE .. WHEN .. expression and CASE Statement SQL 2012.

Your question is not sufficiently clear, but in this CASE (pun half intended), it looks like you might actually want to NEST two CASE expressions and make use of BOTH variants of the CASE syntax:

SELECT TOP 1000 [Id]
  ,[Short]
  ,grade.[Long]
  ,CASE ISNUMERIC(RIGHT( grade.Long , 1 )) 
    WHEN 1 THEN
        CASE WHEN  cast(RIGHT( shiftname.Long , 1 )as int) > 4  THEN 'Qualified' ELSE 'Unqualified Shift' END  
    WHEN 0 THEN 
        'Unqualified Grade'
    END                                                                                 as Grade
FROM [dbo].[lookup_PostGrade] as grade
-- assume join to shiftname is missing

Note that WHEN 0 could be an ELSE.

using > in case statement causes syntax error

1 Answers1

Linked