?
Solved

case statement determination

Posted on 2010-01-11
7
Medium Priority
?
176 Views
Last Modified: 2012-05-08
I have a table where I am trying to determine a resulting value from several fields.  I am running into an issue where the case statement is not correctly determining the value.  Can someone please help?  The code is below.  The results should be the first one should be ROHS2_Unknown and the second should be servex_2.01, but they both are pointing to servex_2.01.
SELECT     [Title],[Material Number],
CASE

WHEN [110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1' AND [93] = '1'   THEN 'servex_2.01'


ELSE 'ROHS2_UNKNOWN'



END AS ROHS2_STATUS,*

FROM         dbo.ROHS_TEMP_SHAREPOINT

Open in new window

output.txt
0
Comment
Question by:Fairfield
[X]
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
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26288655
SELECT     [Title],[Material Number],
CASE WHEN ( [110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') AND [93] = '1'   THEN 'servex_2.01'
ELSE 'ROHS2_UNKNOWN'
END AS ROHS2_STATUS,*
FROM         dbo.ROHS_TEMP_SHAREPOINT
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26288712
Yeah, I think aneeshattingal has what you need.
To explain what the problem was, it looks like a logic issue with the OR and AND combination so if any of the OR columns were 1 it returned the first case value only applying the [93] =1 to the last column [108].
0
 

Author Comment

by:Fairfield
ID: 26293302
There are several conditions I need to check for.  This is a sample of where I am running into issues where it is selecting the first condition, however I need to make sure that it analyzes the ALL conditions before selecting the correct one, is that possible or do I have to put in code to look at blanks as well?

SELECT     [Title],[Material Number],

CASE

WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') THEN 'COMPLY_2.01'

WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') AND
[93] = '1' THEN 'SERVEX_2.01'




ELSE 'ROHS2_UNKNOWN'
END AS ROHS2_STATUS,*
FROM         dbo.ROHS_TEMP_SHAREPOINT

Open in new window

0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26293782
Fairfield,
that seems fine, a case statement will return the first matching result


Aneesh
0
 

Author Comment

by:Fairfield
ID: 26293851
Its not returning to correct set.  I need it to match ALL of the condidtions exactly, if it doesn't I want it to go to the next case.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26293948
SELECT     [Title],[Material Number],

CASE
WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') AND
[93] = '1' THEN 'SERVEX_2.01'

WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') THEN 'COMPLY_2.01'

ELSE 'ROHS2_UNKNOWN'
END AS ROHS2_STATUS,*
FROM         dbo.ROHS_TEMP_SHAREPOINT
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 26295719
You have to switch the order so the most restrictive is first ( the one that has " AND [93] = '1' " added to it as the rest of the comparison is the same between those 2 cases ).
As aneeshattingal said above, it will retrun the FIRST matching case so you have to order them correctly.
SELECT     [Title],[Material Number], 
CASE 
WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND 
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') AND 
[93] = '1' THEN 'SERVEX_2.01' 
WHEN ([110] = '1' OR [111] = '1' OR [112] = '1' OR [113] = '1'  OR [114] = '1') AND 
([77] = '1' OR [99] = '1' OR [101] = '1' OR [105] = '1' OR [108] = '1') THEN 'COMPLY_2.01' 
ELSE 'ROHS2_UNKNOWN' 
END AS ROHS2_STATUS,* 
FROM         dbo.ROHS_TEMP_SHAREPOINT

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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