Query not working as expected

[Stark]

I have a dataset with 5 records where the character field 'Status' is blank
except in one case, where is set to 'C'.

The query:
Select * from MyDs where Status <> "C"
extracts nothing !

I modified a record setting a status field to 'W' and this record gets
extracted with the above select.

The query
Select * from MyDs where Status = " " (and tried also with Status = "",
or Status isNull)
correctly extracts all of the blank status fields.

I am working with the BDE and using local Sql, but I don't recall having had
this problem previously.
You can see something I can't see ?

[Jamie]

Post by Stark
I have a dataset with 5 records where the character field 'Status' is
blank except in one case, where is set to 'C'.
Select * from MyDs where Status <> "C"
extracts nothing !
I modified a record setting a status field to 'W' and this record gets
extracted with the above select.
The query
Select * from MyDs where Status = " " (and tried also with Status =
"", or Status isNull)
correctly extracts all of the blank status fields.
I am working with the BDE and using local Sql, but I don't recall having
had this problem previously.
You can see something I can't see ?

I don't do data base coding and I don't know if this would have any
relevance how ever, If you are placing black data in a field that is
string type. You should not be using the '' to generate it. you should
be using a Null string.. Because the last time I checked, the data base
needs at least something in there, even if it's a null string because
things goes wrong when fetching the source string to define the field
in the data base..

SO, create a string and set it to := #0; or something like that.

This problem have have been corrected since then but its a good start..

[BRoberts]

Post by Stark
I have a dataset with 5 records where the character field 'Status' is blank
except in one case, where is set to 'C'.
Select * from MyDs where Status <> "C"
extracts nothing !
I modified a record setting a status field to 'W' and this record gets
extracted with the above select.
The query
Select * from MyDs where Status = " " (and tried also with Status = "",
or Status isNull)
correctly extracts all of the blank status fields.
I am working with the BDE and using local Sql, but I don't recall having
had this problem previously.
You can see something I can't see ?

Did you try: Select * from MyDs where (Status isNull) or (Status <> "C")

If you have control of the table definition you might consider putting a
default value on the Status column so that it is never Null.

[Andreas Koch]

Post by Stark
Select * from MyDs where Status <> "C"
extracts nothing !
I am working with the BDE and using local Sql, but I don't recall having
had this problem previously.
You can see something I can't see ?

I can only tell from oracle, where any comparison with a NULL value (and
an empty string will be treated as a NULL) will allways evaluate to
false, so you HAVE to use "is (not) null" or "nvl" if a column that may
have null values in it.

[Stark]

Post by Andreas Koch

Post by Stark
Select * from MyDs where Status <> "C"
extracts nothing !
I am working with the BDE and using local Sql, but I don't recall having
had this problem previously.
You can see something I can't see ?

I can only tell from oracle, where any comparison with a NULL value (and
an empty string will be treated as a NULL) will allways evaluate to false,
so you HAVE to use "is (not) null" or "nvl" if a column that may have null
values in it.

I solved the question with Bruce suggestion: Select * from MyDs where
(Status isNull) or (Status <> "C")
But I will also accept the advise to always put a value on the field. Apart
from changing the program, I will also need to convert the current dataset