Quoting Ian P. Christian:
>>> set acl_m3 = ${lookup sqlite {SQLITE_DB SPAM_COUNT}{$value}}
>>> However, this will obviously return nothing if the host has never mailed
>>> before, and the 'Spam total' reports as NULL.
>> set acl_m3 = ${lookup sqlite {SQLITE_DB SPAM_COUNT}{$value}{0}}
>> Will return 0 if the lookup returns nothing.
> I tried that, but unfortunately it doesn't.
> 2006-07-23 10:04:05 1G4Zsb-0002IR-Cx <snip> Warning: Spam total: <NULL>
Ok, as Magnus pointed out, this only works if the query fails or returns
nothing, but NULL actually is something.
Workaround (a little ugly):
> ${lookup sqlite {QUERY}{${if eq {$value}{<NULL>} {0}{$value}}} {0}}
cannot think of anything better right now...
btw, I got additionally confused because sqlite 3.2.2 returns 0 on
SELECT SUM(NULL), while 3.3.3 returns NULL (or nothing? I don't know,
it's way too hot for further investigation).
>>> btw - exim seems to do NO error reporting with sqlite problems
>> uh, that's quite bad, depending on what you are doing with it.
> It's damn annoying, I had to break out strace to try to figure out what
> was going on with this one.
What was the problem you got from that?
> Martin's suggestion (thanks Martin) of doing it in the SQL is the way
> I've been doing it until now - I was just curious as to weather there is
> a better way
The easiest SQL way is "SELECT IFNULL(SUM(something),0) ...". That's
probably better than using Exim magic...
