Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using Case label in where clause?

Posted on 2012-03-15
7
218 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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