On Wed, May 16, 2018 at 8:33 PM Keith Medcalf <[email protected]> wrote:
>
> SELECT coalsce((select action
> from blocked
> where mail='...'), 'OK') as action;
>
Nice one Keith. Works (see below), but I find it a bit intuitive,
since returning no row is somehow not intuitively equivalent (in my mind at
least)
to a scalar row with a NULL value. Your query makes no distinction between
these two cases.
PS: Thanks Ryan, that's what I thought too, and would have been my
work-around too.
C:\Users\ddevienne>sqlite3
SQLite version 3.20.1 2017-08-24 16:21:36
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table blocked (mail text primary key, action text);
sqlite> insert into blocked values ('[email protected]', 'DISCARD');
sqlite> select coalesce((select action from blocked where mail='[email protected]'),
'OK') as action;
OK
sqlite> select coalesce((select action from blocked where mail='[email protected]'),
'OK') as action;
DISCARD
sqlite> select action from blocked where mail='[email protected]';
sqlite> select action from blocked where mail='[email protected]';
DISCARD
sqlite> insert into blocked values ('[email protected]', NULL);
sqlite> select coalesce((select action from blocked where mail='[email protected]'),
'OK') as action;
OK
sqlite> select action from blocked where mail='[email protected]';
sqlite>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
