t-sql 2012 case statement issue

Question

Post reply

t-sql 2012 case statement issue

wendy elizabeth

SSCoach

Points: 16915
More actions
September 10, 2016 at 3:22 pm

#317257

In a t-sql 2012, I have the following case statement:
CASE
WHEN (CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'
AND LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1)
OR
(CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'
AND (LessonPlanActivity.[weight] IS NULL OR (LessonPlanActivity.[weight] <> 1.000 AND LessonPlanActivity.[weight] <> 2.000 AND LessonPlanActivity.[weight] <> 3.000 AND LessonPlanActivity.[weight] <> 4.000)))
THEN 1 --multiplier
ELSE 0
The field called weight defined as (decimal(6,3),null)
Basically when the field called weight does not = the values of 1, 2, 3, or 4 Then the value from the case statement should get a result of 1.
The problem is every time a value of 2.000 is located it is selected while it should not be selected. Thus would you show me the sql on how to fix this problem?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

You've been asking questions here long enough to know we need some sample data and expected output.

Post some up and we'll fix your problem for you.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

ensure your backets are really as they should be.

you got 3 conditions - see which one is "incorrect" according to your expectations.

CASE

WHEN

-- condition 1

(CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School' AND LessonPlanActivity.[weight] IS NULL

-- condition 2

OR LessonPlanActivity.[weight] <> 1)

-- condition 3

OR (CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'AND (LessonPlanActivity.[weight] IS NULL OR (LessonPlanActivity.[weight] <> 1.000 AND LessonPlanActivity.[weight] <> 2.000 AND LessonPlanActivity.[weight] <> 3.000 AND LessonPlanActivity.[weight] <> 4.000)

))

THEN 1 --multiplier

ELSE 0

drew.allen SSC Guru Points: 76985 More actions · Answer 3

A CASE expression is not an IIF expression. You are allowed and even encouraged to have as many separate tests as necessary. This along with the short-circuit evaluation of CASE expressions can make each expression fairly simple. For example, you might rewrite yours as (as close as I can determine)

CASE

WHEN CalendarSchool.schoolLevelOPS NOT IN ('All', 'Elem', 'High') THEN 0

WHEN ScheduleStructure.name NOT IN ('Elementary', 'High/Middle School') THEN 0

WHEN CalendarSchool.schoolLevelOPS = 'Elem' AND ScheduleStructure.name = 'Elementary' THEN 0

WHEN CalendarSchool.schoolLevelOPS = 'High' AND ScheduleStructure.name = 'High/Middle School' THEN 0

WHEN LessonPlanActivity.[weight] IS NULL THEN 1

WHEN LessonPlanActivity.[weight] IN (1, 2, 3, 4) THEN 0

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND LessonPlanActivity.[weight] = 1 THEN 0

ELSE 1

END

The main advantage to this is it's MUCH EASIER to test. Each of the conditions is simple, so it's easy to determine whether a specific record meets those conditions. You can temporarily update the values returned to distinguish which condition a particular record met and therefore which previous conditions it failed to meet.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

You are probably missing a set of parenthesis in the 3rd line of your pasted code

AND LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1)

should be

AND ( LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1 ) )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 5

wendy elizabeth (9/10/2016)
In a t-sql 2012, I have the following case statement:
CASE
WHEN (CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'
AND LessonPlanActivity.[weight] IS NULL OR LessonPlanActivity.[weight] <> 1)
OR
(CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'
AND (LessonPlanActivity.[weight] IS NULL OR (LessonPlanActivity.[weight] <> 1.000 AND LessonPlanActivity.[weight] <> 2.000 AND LessonPlanActivity.[weight] <> 3.000 AND LessonPlanActivity.[weight] <> 4.000)))
THEN 1 --multiplier
ELSE 0
The field called weight defined as (decimal(6,3),null)
Basically when the field called weight does not = the values of 1, 2, 3, or 4 Then the value from the case statement should get a result of 1.
The problem is every time a value of 2.000 is located it is selected while it should not be selected. Thus would you show me the sql on how to fix this problem?

Just out of curiosity, why are you defining LessonPlanActivity.weight four times?

You could just use a LessonPlanActivity NOT IN (1.000,2.000,3.000,4.000), save yourself some typing and make the CASE statement easier to read.

EDIT: Added CODE brackets. And added changed code.

I would do the following:

CASE

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'

AND LessonPlanActivity.[weight] IS NULL

THEN 1 --replacing OR with THEN and new WHEN statement

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'

AND LessonPlanActivity.[weight] <> 1))

THEN 1 --replacing OR with THEN and new WHEN statement

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'

AND (LessonPlanActivity.[weight] IS NULL)

THEN 1 --replacing OR with THEN and new WHEN statement

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'

AND LessonPlanActivity.[weight] IN (1.000, 2.000,3.000,4.000)

THEN 1 --multiplier

ELSE 0

Since the NULL LessonPlanActivity is the equivalent of 1-4 in the Elementary school and is the equivalent of not being 1 in the High / Middle School, I might also do an ISNULL(LessonPlanActivity,<value of choice>) and then remove the other two WHEN clauses. Such as:

CASE

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'Elem') AND ScheduleStructure.name <> 'High/Middle School'

AND ISNULL(LessonPlanActivity.[weight],2.000) <> 1

THEN 1 --replacing OR with THEN and new WHEN statement

WHEN CalendarSchool.schoolLevelOPS IN ('All', 'High') AND ScheduleStructure.name <> 'Elementary'

AND ISNULL(LessonPlanActivity.[weight],1.000) IN (1.000, 2.000,3.000,4.000)

THEN 1 --multiplier

ELSE 0

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.