case statement determination
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
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].
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].
ASKER
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
Fairfield,
that seems fine, a case statement will return the first matching result
Aneesh
that seems fine, a case statement will return the first matching result
Aneesh
ASKER
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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