Perform a where statement off a case statement

I have the following code:

  (CASE
      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?
metalteckAsked:
Who is Participating?
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.

grendel777Commented:
COALESCE(
  (CASE
      WHEN jbc.JOB_CLASS between '110' and '140' then 'Hiring Manager'
        ELSE ''
      END)
, '')
 as "loginGroup",
0
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:

WHERE ISNULL(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

0
PortletPaulfreelancerCommented:
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
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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' 
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

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.
0
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.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
metalteck, do you still need help with this question?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.