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
7
Medium Priority
?
178 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
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

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…

564 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