Re: [sqlite] couldn't use is null function

2012-11-08 Thread Igor Tandetnik 

On 11/8/2012 8:49 PM, YAN HONG YE wrote:

sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s not null and fmn like '85495%';
854954|R
854952|  
//-here is 
NULL, but I don't know why it shows in the not null result


It's not NULL, it's an empty string. Run these statemetns, see for yourself:

SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading 
!= '' and fmn like '85495%';
SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading = 
'' and fmn like '85495%';


--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

2012-11-08 Thread Simon Davies 
On 9 November 2012 01:49, YAN HONG YE  wrote:
> sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE 
> IMPLANTATIONgrading i
> s not null and fmn like '85495%';
> 854954|R
> 854952|  
> //-here is 
> NULL, but I don't know why it shows in the not null result

What you are claiming is null is an empty string; they are not the same thing.

> 854951|R
> 854953|R
> 854950|R
> 854959|G
> 854957|
> 854955|R
> 854956|R
> 854958|

http://en.wikipedia.org/wiki/Null_(SQL)

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

2012-11-08 Thread YAN HONG YE 
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s not null and fmn like '85495%';
854954|R
854952|  
//-here is 
NULL, but I don't know why it shows in the not null result
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE IMPLANTATIONgrading i
s null and fmn like '85495%';
sqlite> SELECT fmn,IMPLANTATIONgrading FROM T93C_ADL WHERE fmn like '85495%';
854954|R
854952|
854951|R
854953|R
854950|R
854959|G
854957|
854955|R
854956|R
854958|
sqlite>



Message: 6
Date: Wed, 7 Nov 2012 08:44:01 +
From: Simon Davies 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] couldn't use is null function
Message-ID:

Content-Type: text/plain; charset=ISO-8859-1

On 7 November 2012 08:27, YAN HONG YE  wrote:
> SELECT * FROM ADL WHERE Project_grading is null;
> can't select anything.

sqlite> create table ADL( id integer primary key, data text,
Project_grading integer );
sqlite> insert into ADL( data, Project_grading ) values( '1', 1 );
sqlite> insert into ADL( data, Project_grading ) values( '2', 2 );
sqlite> insert into ADL( data ) values( '3' );
sqlite> insert into ADL( data, Project_grading ) values( '4', 4 );
sqlite> insert into ADL( data ) values( '5' );
sqlite> insert into ADL( data, Project_grading ) values( '6', 6 );
sqlite>
sqlite> select * from ADL;
1|1|1
2|2|2
3|3|
4|4|4
5|5|
6|6|6
sqlite>
sqlite> select * from ADL where Project_grading is null;
3|3|
5|5|

Regards,
Simon

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

2012-11-07 Thread Igor Tandetnik 
YAN HONG YE  wrote:
> SELECT * FROM ADL WHERE Project_grading is null;
> can't select anything.

So there's no row in ADL where Project_grading is in fact NULL. What makes you 
believe differently?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

2012-11-07 Thread Simon Davies 
On 7 November 2012 08:27, YAN HONG YE  wrote:
> SELECT * FROM ADL WHERE Project_grading is null;
> can't select anything.

sqlite> create table ADL( id integer primary key, data text,
Project_grading integer );
sqlite> insert into ADL( data, Project_grading ) values( '1', 1 );
sqlite> insert into ADL( data, Project_grading ) values( '2', 2 );
sqlite> insert into ADL( data ) values( '3' );
sqlite> insert into ADL( data, Project_grading ) values( '4', 4 );
sqlite> insert into ADL( data ) values( '5' );
sqlite> insert into ADL( data, Project_grading ) values( '6', 6 );
sqlite>
sqlite> select * from ADL;
1|1|1
2|2|2
3|3|
4|4|4
5|5|
6|6|6
sqlite>
sqlite> select * from ADL where Project_grading is null;
3|3|
5|5|

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] couldn't use is null function

2012-11-07 Thread YAN HONG YE 
SELECT * FROM ADL WHERE Project_grading is null;
can't select anything.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users