Using IS NULL in MySQL but cannot figure it out in SQLite

Databases
1

I have a rather complex view where some of the data are null. In the WHERE clause, I want the record to show whether the value is NULL or not.

In MySQL, this syntax works:

(customers.phone.Preferred = 1 OR customers.phone.Preferred IS NULL)

IS NULL isn’t available in SQLite but is rather a 2 arguments:

ifnull(X,Y)

This returns the first non-null of the 2 arguments, but I can’t find a way to test for NULL so the record will display anyway.

Any ideas?

2

All of these syntax should be valid in SQLite

select * from x where y isnull;
select * from x where y notnull;
select * from x where y not null;
select * from x where y is null;
select * from x where y is not null;

However that is assuming the Y is actually NULL… and empty string for example is an empty string… .NOT a NULL value

in which case you would have

select * from x where y=""

in a worst case… cheat

select * from x where ifnull(y,'NULL')='NULL'
3

[quote=38596:@Gary McGuire]IS NULL isn’t available in SQLite but is rather a 2 arguments:

ifnull(X,Y)

[/quote]

Try ISNULL instead of IS NULL. (http://www.sqlite.org/lang_expr.html)

4

Thanks, Guys. This one has been interesting. I’m writing all of my databases in MySQL and then have a Xojo function set that builds identical databases in SQLite. The interesting thing is the way you write a View in MySQL is changed internally. For example I wrote the part of the WHERE clause

(customers.phone.IsValid = 1 OR customers.phone.IsValid IS NULL)

When I’m reading the view from the MySQL information_schema.views the VIEW_DIFINITION field, MySQL has changed that particular line of the WHERE clause is changed to:

((`customers`.`phone`.`IsValid` = 1) or isnull(`customers`.`phone`.`IsValid`))

That actually happens with quite a few things when the view is compiled, so I have written several search and replace functions that I have to use. To get this particular issue to work in SQLite, I have to change it to:

((`customers`.`phone`.`IsValid` = 1) or ifnull(`customers`.`phone`.`IsValid`, NULL))

Notice that I changed isnull to ifnull and then added , NULL before the first closing parenthesis.

I already have a function that does much the same for something else, so it shouldn’t be too bad.