sql select
I am trying to select records that have null values in a specific field
using:
SELECT * FROM table_name WHERE field_name IS NULL
the field_name is a single character field.
This returns ' No rows found' (I know there are records with null values)
I also tried the above leaving out the WHERE statement and it only returned
the records that had a character in the field_name and not those without.
There are other records with null value fields in the same table and it
selects these OK
What can cause this behavior?
SELECT * FROM table_name WHERE length(field_name) = 0
It'll work if the field is null AND if the field has spaces.
"James Dickin" <jp35@dial.pipex.com> wrote in message
news:01bf632e$d0e865c0$8a4301bf@ho-a...
I am trying to select records that have null values in a specific field
using:
SELECT * FROM table_name WHERE field_name IS NULL
the field_name is a single character field.
This returns ' No rows found' (I know there are records with null values)
I also tried the above leaving out the WHERE statement and it only returned
the records that had a character in the field_name and not those without.
There are other records with null value fields in the same table and it
selects these OK
What can cause this behavior?
Manuel A. Daponte Santiago wrote:
> SELECT * FROM table_name WHERE length(field_name) = 0>
> It'll work if the field is null AND if the field has spaces.
If it does, I think it should be a bug. I don't think LENGTH(NULL) should
return 0. I think it should return NULL.
> "James Dickin" <jp35@dial.pipex.com> wrote in message
> news:01bf632e$d0e865c0$8a4301bf@ho-a...
> I am trying to select records that have null values in a specific field
> using:
>
> SELECT * FROM table_name WHERE field_name IS NULL>
> the field_name is a single character field.
> This returns ' No rows found' (I know there are records with null values)
> I also tried the above leaving out the WHERE statement and it only returned
> the records that had a character in the field_name and not those without.
> There are other records with null value fields in the same table and it
> selects these OK
Well, I'm sorry to say it, but it doesn't sound to me like you have any rows
where field_name is null. If you had rows where field_name was blank (" "),
they would still be returned by the second query, the one with no WHERE
clause. Try:
SELECT COUNT(*) FROM table_namethen
SELECT COUNT(*) FROM table_name WHERE field_name IS NOT NULLIf they are the same, you have no null field_name's.
June
--
june_t@hotmail.com
Back from the dead, resuscitated by Godiva chocolates...
Sometimes I will select a cloumn without any conditions in
single character column and find this:
|
|
|
|
|
and so on.....then I run:
update <table_name> set column_a = "" where column_a = " ";
then run the select. So,first, identify what is in there..
..maybe you'll just need to this instead:
select * from table_name where column_name = " "
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!
Hi James,
are you sure that you have NULL values inside the
fields ?
Informix makes a difference between empty fields
and empty strings. An empty string is simply a
stream of blanks. It's easy to enter an empty
string into a column.
create table t1 ( f1 char(10));
insert into t1 ( f1 ) values ( "" );-- this will be an empty string, but Informix
-- fills the string up with blanks
If you want a NULL value you must tell it the
engine.
insert into t1 ( f1 ) values( null );
In the same way you must select the rows.
1) select * form t1 where f1 = "";
2) select * from t1 where f1 is null;
I hope this short description will help you
to find the rows.
Regards
--
Stefan Weideneder
Phone: +49 89/3565478-2 ---------------
--- Fax: +49 89/3565478-3 -------------
------ mailto:/stefan@weideneder.de ---
-------- http://www.weideneder.de -----