#229 Filtering "null" datacell

[P. K.]

When filtering a datacell in DB table with NULL value (e.g. NUMBER=NULL), there occurs an TZParseError with message 'syntax error'. In my application am using 'Filtering according to selected cell', so selecting of empty cell cannot be excluded, DB driver is SQLite.

Here is the source code:
query: TZQuery;
table: TZTable;

the table was created using this SQL query:
query.SQL.Text := 'CREATE TABLE TEMP("CPOLOZ" Integer NOT NULL PRIMARY KEY AUTOINCREMENT, "DATE" Char(10), "CPRACOV" Integer, "CPRACE" Integer, "MNOZSTVI" real);' +
#13#10;
query.ExecSQL;

and contains this data:
query.SQL.Text:='INSERT INTO TEMP(CPOLOZ, DATE, CPRACOV) VALUES(1,"12.01.2017", 11),(2,"12.01.2017", 12),(3,"13.01.2017", 13),(4,"14.01.2017", 14);';
query.ExecSQL;

the used filter:
table.Filter := 'CPRACE = NULL';
table.Filtered := true;

[Fr0sT.Brutal]

NULL cannot be equal to anything. NULL is not a value, it is an abscence of value. So the filter should be CPRACE IS NULL

[EgonHugeist]

I do agree with Fr0sT..
did you test it? If the advice resolves your issue, it would be nice if you close your open tickets.

[EgonHugeist]

I've added a test for column is null and column is not null
everything is fine . Note this is the SQL standard since '92 so i'll close this ticket now

[P. K.]

I did test it and now it works properly :) Thank you!