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
