Solved

Using Case label in where clause?

Posted on 2012-03-15
7
219 Views
Last Modified: 2012-03-15
I have several case statements and I would like to use them in a where clause - if possible.

For example:
...
Case
When assigned_to_group in ('accounting', 'marketing', 'Finance') then 'Admin'
When assigned_to_group in ('Maintenance', 'Electrical', 'Plumbing') then 'Support'
When assigned_to_group in ('SysAd', 'Networking', 'HelpDesk') then 'Department'
end 'Tower'
....
Case
when priority = 0 and Hours_To_Resolve <= 336 then 'True'
when priority = 1 and Hours_To_Resolve <= 72 then 'True'
when priority = 2 and Hours_To_Resolve <= 4 then 'True'
when priority = 0 and Hours_To_Resolve <= 1 then 'True'
else 'False'
end 'Met_Time_to_Resolve'
....


I want to formulate my where clause like:

WHERE case_type = 1 AND tower = 'Admin' AND Met_Time_to_Resolve = 'False'

Possible?

When I use the above I get an error that says
Invalid column name 'tower'
Invalid column name 'Met_Time_to_Resolve'
0
Comment
Question by:edrz01
7 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 250 total points
ID: 37725363
You can't use the new column names, unfortunately you have to recopy the case:

AND tower = 'Admin'
becomes
AND  assigned_to_group in ('accounting', 'marketing', 'Finance')


 Met_Time_to_Resolve = 'False'

becomes


case
when priority = 0 and Hours_To_Resolve <= 336  then 1 else 0
when priority = 1 and Hours_To_Resolve <= 72 then 1 else 0
when priority = 2 and Hours_To_Resolve <= 4 then 1 else 0
when priority = 3 and Hours_To_Resolve <= 1 then 1 else 0   -- you had priorty = 0 twice.  Did you mean priorty = 3?  
END = 0
0
 
LVL 6

Assisted Solution

by:wshark83
wshark83 earned 250 total points
ID: 37725376
the only way to do that is to create the columns first in a temporary table and then use it...otherwise you will have to copy your entire case statement in the where clause
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37725391
Was editing my comment to say you could use a temporary table, but you take a performance hit depending on the number of rows . . . however shark beat me to it!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Closing Comment

by:edrz01
ID: 37725415
Thanks all, can we re-invent SQL???? It seems like the past 2 hurdles I have posted can't be done. Sigh.... Back to the drawing board.
0
 
LVL 25

Expert Comment

by:jogos
ID: 37725443
In the where there may only be 1 ELSE, so corrected the example by: ged325
WHERE 
case
when priority = 0 and Hours_To_Resolve <= 336  then 1 
when priority = 1 and Hours_To_Resolve <= 72 then 1 
when priority = 2 and Hours_To_Resolve <= 4 then 1 
when priority = 3 and Hours_To_Resolve <= 1 then 1 
else 0   -- you had priorty = 0 twice.  Did you mean priorty = 3?  
END = 0

Open in new window


For the temporary table .... that is work (IO) that for every execution must be done.
Repeating a case-condition is only typing during development and performance gain with each execution.
0
 

Author Comment

by:edrz01
ID: 37725554
Wow ged325, that worked great!!!! Thank you.

So now my challenge is to calculate a percentage based on the number of false records returned divded by total number of records (true or false).

Can you assist (if so should I open another question)?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37726014
another question would do.  Try it out, post where you're having issues.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question