case statement

[rds80]

I realized a bit late that the code below doesn't work. If there is an assistant manager, but no manager I still get a blank and that is incorrect.

COALESCE(dbo.udfGetManager(CM.ControlID) + ', ' + dbo.udfGetAssistantManager(CM.ControlID), '')

So this is the replacement code:

CASE WHEN MIN(dbo.udfGetManager(CM.ControlID)) IS NULL THEN CASE WHEN MIN(dbo.udfGetAssistantManager(CM.ControlID)) IS NULL THEN ''
ELSE MIN(dbo.udfGetAssistantManager(CM.ControlID)) END
WHEN MIN(dbo.udfGetManager(CM.ControlID)) IS NOT NULL THEN CASE WHEN MIN(dbo.udfGetAssistantManager(CM.ControlID)) IS NULL THEN MIN(dbo.udfGetManager(CM.ControlID))
ELSE MIN(dbo.udfGetManager(CM.ControlID)) + ', ' + MIN(dbo.udfGetAssistantManager(CM.ControlID)) END
END

I tested it out and it seems to take care of all the different situations. But just wanted to make sure with you guys. Thanks.

 

[gmmastros]

So... if there is no manager, you want to display the assistant manager? If there is neither, then you want to show an empty string? The Coalesce function can handle that for you. Like this...

[tt][blue]
COALESCE(NullIf(dbo.udfGetManager(CM.ControlID), '') + ', ' + dbo.udfGetAssistantManager(CM.ControlID),
dbo.udfGetAssistantManager(CM.ControlID),
'')

[/blue][/tt]

This may not be perfect. What return value to get you from the functions if the manager or assitant does not exist? Anyway, give it a try.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom