Perform a where statement off a case statement

COALESCE(
  (CASE
      WHEN jbc.JOB_CLASS between '110' and '140' then 'Hiring Manager'
        ELSE ''
      END)
, '')
 as "loginGroup",

I believe you're wanting this in the WHERE section, rather than refined in the SELECT section (as above).

Since the essence of your CASE is "jbc.JOB_CLASS between '110' and '140', you can do this with or without a CASE statement.

With the CASE:

WHERE ISNULL(CASE
            WHEN jbc.JOB_CLASS between '110' and '140' then 'Hiring Manager'
              ELSE ''
            END, '') <> ''

Select allOpen in new window

Or, simply put:

WHERE jbc.JOB_CLASS between '110' and '140'

Select allOpen in new window

dsacker is correct in the second code block, BUT....

Question for you though, '110' and '140', does the '' mean that this is a character column and not numeric?
If so, better to cast to numeric, then do the BETWEEN comparison

WHERE CAST(jbc.JOB_CLASS as numeric(19,4))  between 110 and 140) 

Select allOpen in new window

To illustrate...

CREATE TABLE #tmp (no varchar(10)) 
INSERT INTO #tmp (no) VALUES ('110'), ('140'), ('113456856'), (NULL) 

-- Looky here, text compare, '113456856' is considered BETWEEN '110' and '140' 
SELECT *
FROM #tmp
WHERE no BETWEEN '110' AND '140'

-- Numeric compare, This only returns the 110 and 140
SELECT *
FROM #tmp
WHERE CAST(no as numeric(19,4)) BETWEEN 110 AND 140

Select allOpen in new window

The above also doesn't consider what to do if there is a value that can't be converted to a number.  So if some numb nuts entered 'banana', as a job_class, then SQL can't convert to determine if it's between 110 and 140, so you'd have to use ISNUMERIC() to filter those out.

If you want to insure only numeric entries, add the appropriate WHERE condition:

WHERE jbc.JOB_CLASS between '110' and '140' AND
    jbc.JOB_CLASS LIKE '[0-9][0-9][0-9]'

You don't want to use a function on any column in a WHERE if you can avoid it.  And, as noted above, a cast to numeric also will causes failures if there is non-numeric data present.

metalteck, do you still need help with this question?