On Wed, May 16, 2018 at 8:33 PM Keith Medcalf <kmedc...@dessus.com> 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 ('f...@acme.com', 'DISCARD');
sqlite> select coalesce((select action from blocked where mail='f...@acm.com'),
'OK') as action;
OK
sqlite> select coalesce((select action from blocked where mail='f...@acme.com'),
'OK') as action;
DISCARD
sqlite> select action from blocked where mail='f...@acm.com';
sqlite> select action from blocked where mail='f...@acme.com';
DISCARD
sqlite> insert into blocked values ('b...@acme.com', NULL);
sqlite> select coalesce((select action from blocked where mail='b...@acme.com'),
'OK') as action;
OK
sqlite> select action from blocked where mail='b...@acme.com';
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
