Solved

Using Case label in where clause?

Posted on 2012-03-15
7
216 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now