Select case when is not null

From the sample data date 1 is populated first, is this a reasonable assumption or can date 1 be null but date2 or date3 have values?

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. The SQL standard does not specify how to process case expressions—it just defines the result.

Of course, these products eventually deliver the right result—they just discard the unnecessarily evaluated ones. Ultimately, this is a result of the declarative nature of SQL: the database—not the programmer—decides how to get the correct result.

Do not use non-deterministic functions or data-changing functions in case expressions. The undefined evaluation process can have unpredictable side effects. To avoid repeating an operand that is used in all when clauses over and over again, the SQL standard offers the so-called simple case :. The other side of the comparison remains in the when clause. The standard defines the simple case as a transformation to a searched case —the rules described above remain valid.

SQL offers two case abbreviations to cope with null : coalesce and nullif. Both are used like functions and do not use the keywords case , when , then , else and end. Coalesce returns the first not- null parameter or null , if all parameters are null.

The number of parameters is not limited. The standard defines coalesce as a transformation into a case expression. The following expressions are therefore equivalent:. Nullif requires two parameters and generally returns the value of the first one.

Nullif is also defined as a transformation to case and is typically used to prevent division by zero errors:. I make my living from training , other SQL related services and selling my book. SQL has introduced two more abbreviations that extend the simple case. Both are optional features and are not yet widely supported. It also relies on the precedence of when clauses : the first true condition wins. The Overflow Blog. Podcast Explaining the semiconductor shortage, and how it might end.

Does ES6 make JavaScript frameworks obsolete? Featured on Meta. Now live: A fully responsive profile. Related 9. Hot Network Questions. Question feed. Accept all cookies Customize settings. Joe Stefanelli Joe Stefanelli k 17 17 gold badges silver badges bronze badges. The result of the comparison is NULL. Agree that three-valued boolean logic is not for everybody, but you can't say 'it cannot be compared'. Tomalak Tomalak k 62 62 gold badges silver badges bronze badges.

Jeff Sloyer 4, 1 1 gold badge 22 22 silver badges 46 46 bronze badges. Austin7L Austin7L 1 1 1 bronze badge. New contributor. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.