using > in case statement causes syntax error

问题:

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

回答1:

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.