"P Kishor" wrote,
On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
Ok, I have figured out that I do not have null values on the data of my
table, even though I do have empty strings. Here is my schema,
sqlite>
sqlite> .schema
CREATE TABLE LSOpenJobs
(
id integer primary key, ProjID integer, parent, children, login,
cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang,
vendor,
vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid,
notes, status
);
And here is a call for a SELECT for a null value:
sqlite> select * from LSOpenJobs where notes IS NULL;
sqlite>
Now, I know for a fact that there are notes empty on some of the records
there. Why is sqlite thinking that I do not have null values?
you know for a fact that some of the notes are empty, but what makes
you think they are null? Did you create them as null or did you get
this db from someone?
Well, at the beginning I created them as null values. However, I have been
adding data to the records, so it may be that the library that I am using
may not be setting those values to the correct null value. Here is a record
with a few null values:
sqlite> select * from LSOpenJobs where id = '166';
166|166|1172158922388||us|MER|3.0 Cards|Technical|
Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||||||c
the last column is the status, which has the value c and the previus column
is notes, which is empty or null. So, my question is what is NULL for
SQLite? This is a library call ddbi for the D language.
Here is a simple test --
This test below works on a new created DB, but doing a SELECT on my database
using your syntax for null value is not working. I can provide you the DB,
if you want and you will see that I am not crazy. I have been spending two
days on this and I am going to create a new program that shows this bug to
the folks that wrote the library.
I still would like to know what is a null value. It may have to do with
this.
thanks for the help.
Lucknow:~ punkish$ sqlite3
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE foo (a, b);
sqlite> .s
CREATE TABLE foo (a, b);
sqlite> INSERT INTO foo (a) VALUES ('blah');
sqlite> INSERT INTO foo (b) VALUES ('blech');
sqlite> .headers on
sqlite> .mode columns
sqlite> SELECT * FROM foo;
a b
---------- ----------
blah
blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
a b
---------- ----------
blech
sqlite> SELECT * FROM foo WHERE b IS NULL;
a b
---------- ----------
blah
sqlite> UPDATE foo SET b = '' WHERE a = 'blah';
sqlite> UPDATE foo SET a = '' WHERE b = 'blech';
sqlite> SELECT * FROM foo;
a b
---------- ----------
blah
blech
sqlite> SELECT * FROM foo WHERE a IS NULL;
sqlite> SELECT * FROM foo WHERE b IS NULL;
sqlite>
----- Original Message -----
From: "jose isaias cabrera" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Saturday, March 10, 2007 11:40 PM
Subject: [sqlite] UPDATE colomns based on their values
>
> Greetings!
>
> So, I would like to update some columns based on whether or not they
> are
> null. Some of you folks have helped me with ifnull and IS NULL, and I
> happened to see coalesce, but none of these are working. If I set the
> value, they will work, but with the checks, they do not get UPDATEd.
>
> Here is the call:
>
> BEGIN;
> UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND
> bdate
> IS NULL;
> UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE
> ProjID =
> '215';
> UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID =
> '215';
> COMMIT;
>
> As you can see, I am using 3 different checks and none of these are
> working. I know it's something simple, but what it is?
>
> Any ideas?
>
> Maybe the other question is, what defines "IS NULL" or "ifnull" or
> "coalesce"?
>
> thanks,
>
> josé
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
