||Re: filter on logical plus date field
Mervyn Bick <email@example.com>
||Thu, 6 Jan 2022 22:29:16 +0200
On 2022/01/06 21:27, Rouke wrote:
> set filter to not (returned=true or not isblank(dateofrtrn))
> seems to give the required result.
> Any idea how to do this in local sql?
> Where NOT (loans.RETURNED = True OR NOT isblank(loans.DATEOFRTRN))
> doesn't get accepted.
As Ken has said elsewhere in the thread, logical decisions can be
tricky. :-( Adding brackets into the mix can change the whole thing.
Sometimes you need them, most times you don't. The tests inside the
brackets get evaluated first.
set filter to not (returned=true or not isblank(dateofrtrn))
The test inside the brackets will find all records with
returned = true whether dateofrtrn has a value or not
returned = false where dateofrtrn has a value.
The NOT outside the brackets negates the test inside the brackets so it
finds all records with
returned = false where dateofrtrn does not have a value
set filter to returned = false and isblank(dateofrtrn)
will give you exactly the same result and requires less mental
gymnastics to work out what's happening behind the scenes. :-)
Isblank() is a XDML function, not a localSQL function so it won't work
in a WHERE clause in a SELECT statement.
Try the following
.... Where loans.RETURNED = True and loans.DATEOFRTRN is null