Case when is null

If you need to optimize, check the execution plans but I have not noticed much of a difference. Nordic Mainframe Nordic Mainframe I didn't know that. Haven't thought that there are so much possibilities to do that.

There are plenty of solutions but none covers why the original statement doesn't work. NeatNit 1 1 gold badge 2 2 silver badges 12 12 bronze badges. Ian Jacobs Ian Jacobs 5, 1 1 gold badge 22 22 silver badges 37 37 bronze badges. Yeah realized it after I posted. Figured I'd leave it since it solved the part he was having trouble with. The problem is that null is not considered equal to itself, hence the clause never matches. Something unknown, is most likely not the same as something else that is unkown.

Found a solution to this. Paul Roub Kenyadigit Kenyadigit 31 1 1 bronze badge. Frederic 3, 1 1 gold badge 20 20 silver badges 36 36 bronze badges. Linda Linda 11 1 1 bronze badge. I hope this is a helpful alternative. Chef Slagle Chef Slagle 3 3 silver badges 7 7 bronze badges. Are you sure that works? Jason caught an error, so this works Can anyone confirm the other platform versions? Molem Molem 71 1 1 silver badge 3 3 bronze badges.

Pang 8, gold badges 82 82 silver badges bronze badges. In your current test line 1 checks date 1 is greater than date 2 and date 3 Line 2 checks date 2 is greater than date 1 and date 3 Line 2 checks date 3 is greater than date 1 and date 2. Do you need to check date 1 in the second and third tests? If my assumption that date1 must be populate first is correct I would change the logic as follows. When you compare a date to a null value, the equation will not result.

Check out the different outputs from this query:. If you absolutely must, insist on a format that will sort properly. Unless you have insured that a row cannot have the same non-null values for any 2 of date1, date2, and date3, you have to decide how to deal with ties. I assumed date3 beats date2 beats date1 in any ties. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy. Already have an account?

Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox. Funny thing is I stumbled upon this by accident when I was making some code changes the other day and when I tested the code I wondered why the NULL was not being evaluated correctly! Like Like. Thanks for this post. This is why I generally follow a couple of rules: 1 Never use nulls.

You are commenting using your WordPress. You are commenting using your Google account. You are commenting using your Twitter account. You are commenting using your Facebook account.

Notify me of new comments via email. Notify me of new posts via email. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.