and , or

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

and , or

Eggert, Henri

Hi sqlite-users

Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
) )
But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming =
'' ) )

Any help would be appreciated.

Thanks's

Henri


PS : The table has been created with :

CREATE TABLE Data
(
  Id integer primary key        
, Date Date        
, Text Text        
, Comming Text      
, Going Text  
, .....  
, constraint c1 unique ( Date )        
 )


Reply | Threaded
Open this post in threaded view
|

Re: and , or

Gerald Dachs
>
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming '' )
> )

What about this: select Id from Data where ( ( Text = '' ) and ( Comming =
'' ))

Gerald

Reply | Threaded
Open this post in threaded view
|

AW: and , or

Eggert, Henri
In reply to this post by Eggert, Henri
Hi,

I want to select the records which have both columns Text and Comming empty.

I have found that the problem is the column name "Text".
If I replace the column name "Text" by another all works fine.
So I wonder : is "Text" a keyword for sqlite ?

Thank's

Henri

-----Ursprüngliche Nachricht-----
Von: Gerald Dachs [mailto:[hidden email]]
Gesendet: Dienstag, 18. Oktober 2005 14:50
An: [hidden email]
Betreff: Re: [sqlite] and , or

>
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming '' )
> )

What about this: select Id from Data where ( ( Text = '' ) and ( Comming =
'' ))

Gerald

Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Markus Weissmann
Hi Henri,

On 18.10.2005, at 14:56, Eggert, Henri wrote:

> I have found that the problem is the column name "Text".
> If I replace the column name "Text" by another all works fine.
> So I wonder : is "Text" a keyword for sqlite ?
>

It indeed is [1]. :)


-Markus

[1] http://www.sqlite.org/datatype3.html

>
> -----Ursprüngliche Nachricht-----
> Von: Gerald Dachs [mailto:[hidden email]]
> Gesendet: Dienstag, 18. Oktober 2005 14:50
> An: [hidden email]
> Betreff: Re: [sqlite] and , or
>
>
>>
>> Hi sqlite-users
>>
>> Works fine : select Id from Data where ( ( Text = '' ) or  
>> ( Comming = ''
>> ) )
>> But NOT    : select Id from Data where ( ( Text = '' ) and  
>> ( Comming '' )
>> )
>>
>
> What about this: select Id from Data where ( ( Text = '' ) and  
> ( Comming =
> '' ))
>
> Gerald
>
>

---
Markus W. Weissmann
http://www.mweissmann.de/
http://www.opendarwin.org/~mww/

Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Gerald Dachs
In reply to this post by Eggert, Henri
> Hi,
>
> I want to select the records which have both columns Text and Comming
> empty.
>
> I have found that the problem is the column name "Text".
> If I replace the column name "Text" by another all works fine.
> So I wonder : is "Text" a keyword for sqlite ?

Why do you wonder? You used it yourself as keyword when you declared "text"
as type text.

Gerald

Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Dennis Cote
In reply to this post by Eggert, Henri
Eggert, Henri wrote:

>Hi,
>
>I want to select the records which have both columns Text and Comming empty.
>
>I have found that the problem is the column name "Text".
>If I replace the column name "Text" by another all works fine.
>So I wonder : is "Text" a keyword for sqlite ?
>
>
>  
>
Henri,

The rules regarding the use of keywords as identifiers are discussed at
http://www.sqlite.org/lang_keywords.html. The word "text" is recognized
as a data type by sqlite but it is not a keyword. It can safely be used
as a column identifier without quoting as shown below using the sqlite3
shell:

sqlite> create table t4(id integer primary key, text text, other text);
sqlite> insert into t4 values(NULL, 'a', 'b');
sqlite> insert into t4 values(NULL, 'c', '');
sqlite> insert into t4 values(NULL, '', 'd');
sqlite> insert into t4 values(NULL, '', '');
sqlite> select * from t4;
id          text        other
----------  ----------  ----------
1           a           b
2           c
3                       d
4
sqlite> select * from t4 where text = '' and other = '';
id          text        other
----------  ----------  ----------
4
sqlite>

Are you using a wrapper or some other interface to access sqlite?

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Kurt Welgehausen
In reply to this post by Markus Weissmann
> Hi Henri,
>
> On 18.10.2005, at 14:56, Eggert, Henri wrote:
>
> > I have found that the problem is the column name "Text".
> > If I replace the column name "Text" by another all works fine.
> > So I wonder : is "Text" a keyword for sqlite ?
> >
>
> It indeed is [1]. :)
>
>
> -Markus
>
> [1] http://www.sqlite.org/datatype3.html


Actually, 'text' is not a designated keyword; see
<http://www.sqlite.org/lang_keywords.html>. In any
event, being a keyword would not explain why the
query with 'or' works and the one with 'and' does
not, or why both queries work in older versions of
Sqlite3.

This seems to be a recently introduced change in
behavior; whether it's a bug is perhaps a matter
of opinion.

Regards
Reply | Threaded
Open this post in threaded view
|

Re: and , or

D. Richard Hipp
In reply to this post by Eggert, Henri
"Eggert, Henri" <[hidden email]> wrote:
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming =
> '' ) )
>

Both work fine when I try them.  Why do you think the second one
is not working?

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

AW: and , or

Eggert, Henri
In reply to this post by Eggert, Henri
Sorry , I was not clear enough.

Considere the following :

create table t1 ( a integer , b text , c text ) ;
insert into t1 ( a , b , c ) values ( 1 , 'a' , 'b' ) ;
insert into t1 ( a , b , c ) values ( 2 , 'c' , ''  ) ;
insert into t1 ( a , b , c ) values ( 3 , ''  , 'd' ) ;
insert into t1 ( a , b , c ) values ( 4 , ''  , ''  ) ;
insert into t1 ( a , b     ) values ( 5 , 'e'       ) ;
insert into t1 ( a ,     c ) values ( 6       , 'f' ) ;
insert into t1 ( a         ) values ( 7             ) ;
select a from t1 where ( b ='' and c = '' )

This returns 4 ( b and c have explicitely been set to empty string )
But not 7 ( b and c not set but also empty ).

What I need is a select statement which returns both.

Regards , Henri


By the way :

Sqlite is a great piece of software.
Thank you drh.

-----Ursprüngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]]
Gesendet: Dienstag, 18. Oktober 2005 20:05
An: [hidden email]
Betreff: Re: [sqlite] and , or

"Eggert, Henri" <[hidden email]> wrote:
> Hi sqlite-users
>
> Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
> ) )
> But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming =
> '' ) )
>

Both work fine when I try them.  Why do you think the second one
is not working?

--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Klint Gore
On Wed, 19 Oct 2005 09:59:49 +0200, "Eggert, Henri" <[hidden email]> wrote:
> What I need is a select statement which returns both.

try
select Id from Data
where (coalesce(text,'') = '')
and (coalesce(comming,'') = '');

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : [hidden email]           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+
Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Martin Engelschalk
In reply to this post by Eggert, Henri
Hi all,

The problem seems to be that sqlite makes a difference between an empty
string and a null value.
Therefore, your query has to check both.
In Oracle (and problaby others) the datatype "Varchar2" can be used to
treat an empty string as a null value.
Does anyone know if there is a way to do this in sqlite?

Martin

Eggert, Henri schrieb:

>Sorry , I was not clear enough.
>
>Considere the following :
>
>create table t1 ( a integer , b text , c text ) ;
>insert into t1 ( a , b , c ) values ( 1 , 'a' , 'b' ) ;
>insert into t1 ( a , b , c ) values ( 2 , 'c' , ''  ) ;
>insert into t1 ( a , b , c ) values ( 3 , ''  , 'd' ) ;
>insert into t1 ( a , b , c ) values ( 4 , ''  , ''  ) ;
>insert into t1 ( a , b     ) values ( 5 , 'e'       ) ;
>insert into t1 ( a ,     c ) values ( 6       , 'f' ) ;
>insert into t1 ( a         ) values ( 7             ) ;
>select a from t1 where ( b ='' and c = '' )
>
>This returns 4 ( b and c have explicitely been set to empty string )
>But not 7 ( b and c not set but also empty ).
>
>What I need is a select statement which returns both.
>
>Regards , Henri
>
>
>By the way :
>
>Sqlite is a great piece of software.
>Thank you drh.
>
>-----Urspr?ngliche Nachricht-----
>Von: [hidden email] [mailto:[hidden email]]
>Gesendet: Dienstag, 18. Oktober 2005 20:05
>An: [hidden email]
>Betreff: Re: [sqlite] and , or
>
>"Eggert, Henri" <[hidden email]> wrote:
>  
>
>>Hi sqlite-users
>>
>>Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = ''
>>) )
>>But NOT    : select Id from Data where ( ( Text = '' ) and ( Comming =
>>'' ) )
>>
>>    
>>
>
>Both work fine when I try them.  Why do you think the second one
>is not working?
>
>--
>D. Richard Hipp <[hidden email]>
>
>  
>
Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Gerry Snyder
Martin Engelschalk wrote:
> Hi all,
>
> The problem seems to be that sqlite makes a difference between an empty
> string and a null value.
> Therefore, your query has to check both.
> In Oracle (and problaby others) the datatype "Varchar2" can be used to
> treat an empty string as a null value.
> Does anyone know if there is a way to do this in sqlite?

Martin,

When you create the table you can tell sqlite to use an empty string as
the default value if nothing is entered.

Check the column-constraint description in the CREATE TABLE writeup on
the syntax web page.

Gerry

--
------------------
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

Reply | Threaded
Open this post in threaded view
|

Re: AW: and , or

Eric Bohlman
In reply to this post by Martin Engelschalk
Martin Engelschalk wrote:
> The problem seems to be that sqlite makes a difference between an empty
> string and a null value.

s/sqlite/SQL/

The SQL standards all say that nulls never compare equal to anything,
not even other nulls.  SQLite's behavior here is the correct one.