Solved

# case statement determination

Posted on 2010-01-11
Medium Priority
176 Views
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
``````
output.txt
0
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
• 3
• 2
• 2

LVL 75

Expert Comment

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

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

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
``````
0

LVL 75

Expert Comment

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

Aneesh
0

Author Comment

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

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

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
``````
0

## Featured Post

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…
###### Suggested Courses
Course of the Month10 days, 20 hours left to enroll