Can't you just use IFNULL to assign a default value? CASE IFNULL( x, -999 ) WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN -999 THEN 55 ELSE 66 END
On 5 July 2018 at 11:35, R Smith <ryansmit...@gmail.com> wrote: > On 2018/07/05 8:44 AM, Simon Slavin wrote: > >> On 5 Jul 2018, at 7:30am, Clemens Ladisch <clem...@ladisch.de> wrote: >> >> The expression "x = x" will fail for NULL, but succeed for everything >>> else. So you can use that to implement a "not-NULL ELSE" >>> >> Wow. That has to be the most counter-intuitive feature of SQLite. I >> understand why it works, but I still don't like it. Thanks for posting it. >> > > That's how it works everywhere, not just in SQLite. NULL has special > handling in that any expression or function that gets touched by a NULL > value immediately returns NULL (except for some aggregates that sometimes > have NULL values among their input populations, which they simply ignore). > > What the OP essentially wants is to test for NULL values, which is > possible using "IS" but not in an equality test (since the expression [ a = > x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL, as > they should), so it cannot use equality testing in the usual way a CASE > executes. > > My typical way to do this is: > > CASE > WHEN x IS NULL THEN ... > WHEN x < 1 THEN ... > WHEN x < 3 THEN ... > WHEN x < 5 THEN ... > ELSE ... > END; > > But I feel like the equality check option can easily be enhanced in SQLite > to have this work: > > CASE x > WHEN IS NULL THEN .... > WHEN 1 THEN ... > WHEN 3 THEN ... > WHEN 5 THEN ... > END; > > but then it's so little difference from the example above it that I have > never yearned for it - in fact, I never use this latter version due to its > shortcomings in testing anything that is not an equality check (but since > my preference is no measure of its utility, perhaps it's worth considering). > > > Cheers, > Ryan > > PS: Here is a version of the 1st example working: > > WITH C(x) AS ( > SELECT NULL > UNION ALL > SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL > ) > SELECT x, CASE > WHEN x IS NULL THEN 'None' > WHEN x < 1 THEN 'Zero' > WHEN x < 3 THEN 'Small' > WHEN x < 6 THEN 'Medium' > ELSE 'Large' > END AS size > FROM C > ; > > > -- x | size > -- ---- | ------ > -- NULL | None > -- 1 | Small > -- 2 | Small > -- 3 | Medium > -- 4 | Medium > -- 5 | Medium > -- 6 | Large > -- 7 | Large > -- 8 | Large > -- 9 | Large > -- 10 | Large > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
