Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Case label in where clause?

Posted on 2012-03-15
7
Medium Priority
?
226 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 41

Accepted Solution

by:
Kyle Abrahams earned 1000 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 1000 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 41

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 41

Expert Comment

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

581 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