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
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
> My typical way to do this is:
> WHEN x IS NULL THEN ...
> WHEN x < 1 THEN ...
> WHEN x < 3 THEN ...
> WHEN x < 5 THEN ...
> ELSE ...
> 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 ...
> 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).
> 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 mailing list