debug query - 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

Transact-SQL (2000)

debug query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-28 : 12:54:30

what's wrong with the following:

select * from anp where (stage=0) and [status]!='100%'

I have 50 records in the table

if i do

select * from anp where (stage=0) and [status]='100%' I get 7 but when I do the first sql statemetn I get done when I should get 43 -- what am i doing wrong?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-28 : 13:17:15

You are getting no rows because the data does not satisfy your selection criteris. The two queries are not logical opposites.

Since you did not post the DDL and sample data for the table, I cannot give you the exact reason it returns no rows.

More than likely, the reason is that the remaining rows in table anp have NULLS in columns stage or status, or have non-zero values in column stage, or have values of '100%' in column status.

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-28 : 13:32:03

why are they not opposites.

how do I got the records where stage =0 and status not = '100%'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-28 : 13:55:51

You did get the rows "where stage =0 and status not = '100%'". There aren't any. I suspect that you are really after something else.

Since you didn't bother with my hint about posting the DDL and sample data for the table, I guess you prefer to solve this yourself.

That's a good plan, because a few frustrating hours (or days) spent looking for an answer is a great way to remember the lesson of why your query isn't working.

CODO ERGO SUM

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-28 : 14:03:16

the table has

id int
name varchar
status varchar
stage int

all i'm trying to do is get all records where stage=1 and status<>'100%'

what am i doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-28 : 15:07:13

Nothin wrong. The query is syntax-correct but you have no data that fit the requirement.
What MVJ says, is that there is no way to tell that a record with stage = 0 the status MUST be '100%'.

Consider this sample/test data


Stage Status
----- ------
0       100%
0        50%
1       100%
1        50%

Stage = 0 and Status != 100% will give you the 2nd record, if present in your environment.
Stage = 0 and Status = 100% will give you the 1st record, if present in your environment.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-29 : 00:08:40

no becasue if I search for
select * from anp where status!=100% I get 0 records - I should get 43 as only 7 out of 50 have a status of 100% -- if I am doing something wrong -- how can I simply select all records where the status is not 100% (status is a string field)

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-01-29 : 01:05:05

quote:
Originally posted by esthera

no becasue if I search for
select * from anp where status!=100% I get 0 records - I should get 43 as only 7 out of 50 have a status of 100% -- if I am doing something wrong -- how can I simply select all records where the status is not 100% (status is a string field)

Why don't u substract the last character from the status and compare?
i.e. ... Status <> SubString(<Status Value>,1,(Len(<Status Value>)-1))
... etc

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-29 : 01:10:36

quote:
Originally posted by esthera

no becasue if I search for
select * from anp where status!=100% I get 0 records - I should get 43 as only 7 out of 50 have a status of 100% -- if I am doing something wrong -- how can I simply select all records where the status is not 100% (status is a string field)

Are there any spaces or any other special character in your db???

Chirag

http://chirikworld.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-29 : 01:11:26

status is not always a % it's a string

i don't understand why it is not working to say != (or is it<> -- I tried both and they both don't work)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-01-29 : 01:32:44

May because of collation.. try out following stuff..

select * from anp where (stage=0) and [status]!='100%'
Collate latin1_general_cs_as

Chirag

http://chirikworld.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-01-29 : 02:24:16

no i found the problem

when i did !='100%' it did not return the nulls
now i did !='100%' and status is null and that gave me the right records

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-29 : 09:45:32

quote:
Originally posted by esthera

no i found the problem

when i did !='100%' it did not return the nulls
now i did !='100%' and status is null and that gave me the right records

So, it took you 13 hours to figure out it was what I told you on my first reply?