You are correct. Use Is Null or Is not null to check for empty fields in SQL.
2 of 2 people found this helpful
Yes, you can use
WHERE field IS NULL
(or IS NOT NULL) and get rid of the second parameter
btw., in FileMaker it is the better practice to use
IsEmpty ( someField ), rather than someField = ""
1 of 1 people found this helpful
when I have a 1/0 set up, I auto-enter the "0" into the field (replace existing empty fields). Then I toggle with
Abs(myfield - 1)
to always have 0 or 1
That avoids the NULL / NOT NULL
you can also test for NOT 1 for the "false" values.
Then I toggle with
Abs(myfield - 1)
not ( myField )
two different styles?
So add third
1 - myfield
I like "not", since it can correct to logical value if there is other value than 0 or 1 by accident.
That's a good point
Also, 'not' expresses semantically that the numeric value you're operating on acts as a Boolean value.
I still make the field as numeric only, regardless of way it is "toggled" on/off (true/false).
yep! and the reason I make sure there is a value to test.
IS NOT 1
could be NULL in SQL, IsEmpty() - in FMP, or any value (even text or 0)
Wow, that's concerning if FMP regards a null (using SQL) the same as "empty" (blank) since that's not what NULL means.
But, as long as you have 1, 0, and "blank", you can interpret that any way you want, even if FMP's SQL isn't consistent with industry standards.... as in, SELECT NULL = NULL (is null).
Sorry. That is how most consider 'empty'. There is a distinct difference between never having a value and having had a value, but now blank. However for all practical purposes there is no difference for IsEmpty().
And I agree with three possible values which are 'Boolean' and should only be true OR false.
Thus my insistence on using
IS NOT 1
instead of bothering with NULL (as far as eSQL that is)
And insisting that there must be a value in the field so tested. (Auto-enter "0")
Sent from miPhone
I'm sure your way is fine for FMP and better than most.
MySQL, for example, has an actual "binary" type which also allows nulls.
Thus, if you were collecting true or false data, there might be a case you "didn't know". But, if you did a SELECT looking for and empty field when it was actually NULL, you would not get back any records.
Since belt is NULL, I have to do it like this:
OR, Like this:
Article listed below.
It’s probably best to begin our discussion of NULL with a few words ab...out what NULL is not:
- NULL is not the number zero.
- NULL is not the empty string (“”) value.
Rather, NULL is the value used to represent an unknown piece of data.
Avoid one of the most common database mistakes: testing an operand for a NULL value by comparing it to the empty string or zero is not correct!