If I have a boolean field _Reconciled do I have to write

_Reconciled IS NULL OR NOT _Reconciled

or is

NOT _Reconciled

still safe?

Roy Lambert

I'll answer my own question


select * from transactions where
(_reconciled is null or _reconciled = false)

Returns 7891 rows

select * from transactions where
(_reconciled is null or not _reconciled)

Returns 36 as does

select * from transactions where
_reconciled is null

select * from transactions where
_reconciled = false

Returns 7855 as you would expect

But the puzzeling one is

select * from transactions where
not _reconciled

Which returns (wait for it) 7891


Can someone enlighten me?


Roy Lambert

And more fun.


SELECT * FROM Transactions
WHERE
NOT _Reconciled
AND
_fkAccounts = 5

================================================================================
SQL Error (ElevateDB 1.01 Build 1)
================================================================================

Access violation at address 005607B6 in module 'edbmgr.exe'. Read of address
00000008

Roy Lambert

Roy,

To avoid future problems, simply declare them all NOT NULL -
I know how you "like" null values


Michael


Roy Lambert wrote:
> If I have a boolean field _Reconciled do I have to write
>
> _Reconciled IS NULL OR NOT _Reconciled
>
> or is
>
> NOT _Reconciled
>
> still safe?
>
> Roy Lambert

Michael

>To avoid future problems, simply declare them all NOT NULL -
>I know how you "like" null values

Interesting approach. But if I'd followed it I wouldn't have a bug to report to Tim

Roy Lambert

Roy,

<< But the puzzeling one is

select * from transactions where
not _reconciled

Which returns (wait for it) 7891 >>

It seems to be a problem with a live query. Make your query canned and see
what you get.

Ole Willy Tuv

Roy,

<< It seems to be a problem with a live query. Make your query canned and
see what you get. >>

Nah, it seems to be a problem with boolean operators in canned queries also.

The expressions "_reconciled = false" and "not _reconciled" are equivalent
and should return the same result.

Tim said in another thread that there bugs in build 1 related to boolean
evaluation that are fixed in the upcoming build 2.

Ole Willy Tuv

Roy,

<< Can someone enlighten me? >>

It's an issue with live queries and single-column Boolean expressions.  If
you use this instead, it will work:

select * from transactions where
not (_reconciled = TRUE)

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< And more fun. >>

Same issue, different result.

--
Tim Young
Elevate Software
www.elevatesoft.com