Problem with nulls in select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with nulls in select

Author

Topic

tiipe
Starting Member

2 Posts

Posted - 2009-01-13 : 09:00:44

I have table with a char primary key and a int type-field. In the primary key field 80% of the values only contain numbers and the 20% that don't can be filtered by the value of the type.

To illustrate:


select * from x_table where type != 5

would get me all the data where I only have numbers in the primary key.

I want to be able to narrow down a certain set based on the primary key like this:


select * from x_table where type != 5 and key between 10 and 20

Now here comes the difficult part. The type allows null values and I need records with null type to be included in the result set. This won't work.

I tried this:


select * from x_table where (type != 5 OR type is null) and key between 10 and 20

This gives and error

quote:
Conversion failed when converting the varchar value 'JADIJADA' to data type int.

This leaves me baffled because the value that gives the error is from a record that has type = 5.

So my question is, why does including a null comparison in any shape or form (ISNULL...) change the result set against which the second condition is compared? Is the any way around this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 09:09:43

what about this?


select * from x_table where (type != 5 OR type is null) 
and (isnumeric(key)=0 or key between 10 and 20)

tiipe
Starting Member

2 Posts

Posted - 2009-01-13 : 09:15:34

quote:
Originally posted by visakh16

what about this?

select * from x_table where (type != 5 OR type is null)
and (isnumeric(key)=0 or key between 10 and 20)

That works. Thank you!

Do you have any idea why the comparison against null changes the way the query works?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 09:24:58

because it bypasses the first condition (type != 5) altogether and brings records of type = 5. This causes records with non numeric key value to be included which will fail the second filter (key between 10 and 20) as you're trying to compare varchar value to int value which it cant implicitly convert being nonnumeric. so what i've done is to include another handle which satisfies the problem scenario which bypasses the above condition check and avoids the implicit conversion and hence the error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources