Perform a where statement off a case statement

I have the following code:

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

I need to create a statement in my where clause that will exclude any null values that the case statement may bring.
How can I do this?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

      WHEN jbc.JOB_CLASS between '110' and '140' then 'Hiring Manager'
        ELSE ''
, '')
 as "loginGroup",
dsackerContract ERP Admin/ConsultantCommented:
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:

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

Open in new window

Or, simply put:

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

Open in new window

PortletPaulEE Topic AdvisorCommented:
There is no advantage in using that case expression in the where clause. Just filter for job_class between the 2 values and this will exclude all rows with a null in job_class

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim HornMicrosoft SQL Server Data DudeCommented:
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) 

Open 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' 
FROM #tmp
WHERE no BETWEEN '110' AND '140'

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

Open 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.
Scott PletcherSenior DBACommented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
metalteck, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.