Simple but Stupid... - 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 2000 Forums

SQL Server Development (2000)

Simple but Stupid...

Author

Topic

kwilliams

194 Posts

Posted - 2005-03-16 : 17:46:50

I'm losing my mind over this one. I have a simple SQL query:

SELECT *
FROM dbo.DATABASENAME
WHERE Column1 != '0' (same as Column <> '0' in VB)

that's not working. If I change the WHERE clause to:
WHERE Column1 = '0'
...the correct results pull up.

The column I'm referencing is a decimal datatype with a length limit of 9 char's. Its value is set to numero zero, and it comes from a SQL Server 2k DB table. My simple goal is to display all records where Column1 does not equal "0".

If anyone sees what in the heck I'm doing wrong, and can help me to solve it, it would be GREATLY appreciated. Thanks for any help.

KWilliams

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

MattieBala69
Starting Member

30 Posts

Posted - 2005-03-16 : 17:54:39

SELECT *
FROM dbo.DATABASENAME
WHERE Column1 > 0

kwilliams

194 Posts

Posted - 2005-03-16 : 17:58:02

I tried:
SELECT *
FROM dbo.DATABASENAME
WHERE Column1 > 0
...and
SELECT *
FROM dbo.DATABASENAME
WHERE Column1 > '0'
...but neither worked. I know that the solution has to be something simple. I really thought that would work.

Katherine Williams
-------------------
It's the end of the world as we know it...and I feel fine

PW
Yak Posting Veteran

95 Posts

Posted - 2005-03-16 : 17:58:10

If the column is decimal, why are you comparing it to a string ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-16 : 18:09:13

When you say "not working" what do you mean? errors? values of 0? what?

Be One with the Optimizer
TG

kwilliams

194 Posts

Posted - 2005-03-17 : 09:18:15

quote:
If the column is decimal, why are you comparing it to a string ?

On the front-end ASP page, I'm trying to display all records where the value of one of Column1 equals zero.

quote:
When you say "not working" what do you mean? errors? values of 0? what?

As I stated in my first post, no values come back; no errors and no values of 0. If I reverse it and have Column1 = '0', all of the records that's value equals 0 do come up.

I've also tried using the LIKE operator for this, just to see what would happen:
WHERE NOT Column1 LIKE '0%'
and
WHERE NOT Column1 LIKE '%0%'
..and still no records were returned. I thought that logically if the equality operator works, that the inequality operator should work also. Am I wrong in that assessment? Could the fact that the column's datatype is "decimal" have anything to do with it? And if so, why does it work with an equality operator? Thanks for any help.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-17 : 09:35:22

I'm still not clear on what the problem is but does this work for you?


set nocount on
declare @tb Table (Column1 decimal(9,4))
insert @tb
select null union
select	0 union
select	0.01 union
select	12.345 union
select	0.012

sElect * from @tb where isNull(Column1,-1) <> 0 --returns nulls
sElect * from @tb where Column1 <> 0 --ignores NULLs

Be One with the Optimizer
TG

kwilliams

194 Posts

Posted - 2005-03-17 : 09:45:15

quote:
I'm still not clear on what the problem is...

There is a SQL Server 2k DB table that contains several columns, including several with the datatype of decimal. This table is gathering votes for our future election, so the value of these columns is going to be one of three things:
1) "0" for candidates that aren't running for that precinct
2) "null" for candidates running for that precinct, but don't have votes in yet.
3) A number value for candidates running for that precinct that do have votes in.

I have an internal form page in which I want to pull only those candidates that's column value does not equal 0. It should pull up only those candidates that either have null values or have a number value entered. Any candidates with a 0 value entered should not pull up. But it's not pulling up any of the candidates when I use: WHERE Column 1 <> '0', and is pulling up the correct candidates if I use: WHERE Column 1 = '0'.

So I simply want to display only those candidates whose value for Column1 does not equal zero.

quote:
...but does this work for you?

Yes this one worked for me:

sElect * from @tb where isNull(Column1,-1) <> 0 --returns nulls

Thanks so much for your help. I greatly appreciate it, and the help from this forum as a whole. You've really helped to clear my head, as much as it can be at least. Thanks.

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-18 : 00:09:05

Really its simple, but we need to concentrate more on required times

:) While we stop to think, we often miss our opportunity :)

kwilliams

194 Posts

Posted - 2005-03-18 : 09:22:53

quote:
Really its simple, but we need to concentrate more on required times

huh?

KWilliams
-------------------
It's the end of the world as we know it...and I feel fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources