Is not Null clause not working for me

I've got a strange problem with my sql not working. I'm trying to query a
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:

> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:

> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>