CASE WHEN Multiple Choices

Question

CASE WHEN Multiple Choices

LHendren

SSC Veteran

Points: 282
More actions
March 26, 2010 at 3:57 pm

#220955

I have a GridView that I am trying to display projects by Project Manager ID's via a DropDown pick. Only some of the projects have been assigned Project Managers; therefore, a vast majority are NULL. The abbreviated code is:
AND (P.PAprojmngrid =
CASE
WHEN @Discipline = 'Surveying' THEN '07'
WHEN @Discipline = 'Civil Engineering' THEN '04'
WHEN @Discipline = 'Geotechnical' THEN '03'
WHEN @Discipline = 'Materials Testing' THEN '06'
WHEN @Discipline = 'Combined' THEN What goes here??
END)
When Combined is selected from my DropDown, I would like to display all rows that have a project manager assigned ('07' + '04' + '03' + '06') but not rows that have no project managers assigned.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

LHendren (3/26/2010)
I have a GridView that I am trying to display projects by Project Manager ID's via a DropDown pick. Only some of the projects have been assigned Project Managers; therefore, a vast majority are NULL. The abbreviated code is:
AND (P.PAprojmngrid =
CASE
WHEN @Discipline = 'Surveying' THEN '07'
WHEN @Discipline = 'Civil Engineering' THEN '04'
WHEN @Discipline = 'Geotechnical' THEN '03'
WHEN @Discipline = 'Materials Testing' THEN '06'
WHEN @Discipline = 'Combined' THEN What goes here??
END)
When Combined is selected from my DropDown, I would like to display all rows that have a project manager assigned ('07' + '04' + '03' + '06') but not rows that have no project managers assigned.

You can do it like this

AND NOT ( P.PAprojmngrid IS NULL ) -- you don't ever want the NULLs, so eliminate them here

AND (

( P.PAprojmngrid =

CASE

WHEN @Discipline = 'Surveying' THEN '07'

WHEN @Discipline = 'Civil Engineering' THEN '04'

WHEN @Discipline = 'Geotechnical' THEN '03'

WHEN @Discipline = 'Materials Testing' THEN '06'

END

)

OR

( @Discipline = 'Combined' AND P.PAprojmngrid IN ('07','04','03','06') )

)

Are you sure you have this query right in the first place? Do you really select a project manager id by selecting a "discipline"? What if you have more than one project manager per discipline?

Also, if it was me, the drop down list would contain the names and corresponding IDs so that you can pass the ID to the query and do away with that horrible CASE statement.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You can also do it this way:

AND (P.PAprojmngrid =

CASE

WHEN @Discipline = 'Surveying' THEN '07'

WHEN @Discipline = 'Civil Engineering' THEN '04'

WHEN @Discipline = 'Geotechnical' THEN '03'

WHEN @Discipline = 'Materials Testing' THEN '06'

WHEN @Discipline = 'Combined' THEN P.PAprojmngrid

END)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Matt Miller (#4) (3/26/2010)
You can also do it this way:
AND (P.PAprojmngrid =
CASE
WHEN @Discipline = 'Surveying' THEN '07'
WHEN @Discipline = 'Civil Engineering' THEN '04'
WHEN @Discipline = 'Geotechnical' THEN '03'
WHEN @Discipline = 'Materials Testing' THEN '06'
WHEN @Discipline = 'Combined' THEN P.PAprojmngrid
END)

It's not obvious that this excludes NULLS - until you switch on your brain... of course NULL doesn't equal NULL - it can't it's NULL!

This is much nicer, as long as those four values (and NULLS) are the only ones...or if Combined should include any value other than NULL.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

LHendren SSC Veteran Points: 282 More actions · Answer 4

Mister.Magoo,

Thank you for your help. AND NOT (P.PAprojmngrid IS NULL) was the key. It now works perfectly.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

You're welcome - thanks for the feedback.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);