Rule BP011 (A comparison or expression is using NULL without explicit provision for a NULL value.)

BrienKing

This rule is not appropriate for a CASE statement.

CASE [s2].[SchoolID]
WHEN NULL THEN .[SchoolNumber]
ELSE [s2].[SchoolNumber]
END AS [SchoolNumber]

Unless what you're saying is that .SchoolNumber could also be NULL?

RichardL

Hi @BrienKing

Thanks for your post.

Could you please explain the nature of your issue in further depth so that we can investigate for you?

BrienKing

The error is "A comparison or expression is using NULL without explicit provision for a NULL value.", yet in the example posted above (not sure why it has strike out in it), the CASE statement has an explicit check for NULL with WHEN NULL THEN...

The case can also have an ELSE condition that should catch everything else, including a NULL value.

So this error should only show up if none of the WHEN conditions don't explicitly check for NULL and there is no ELSE on the CASE.

RichardL

Hi @BrienKing

Thanks again for your post. 

It looks like you have a support contract with us. Could you please send us a ticket so that we can investigate this for you? Can you please include your SQL snippet that gives this response in your ticket as well? 

Kind regards

Richard Lynch.