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

x
Solved

# case statement determination

Posted on 2010-01-11
Medium Priority
178 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
• 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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
###### Suggested Courses
Course of the Month11 days, 19 hours left to enroll