Is there anything wrong with this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is there anything wrong with this query?

Author

Topic

jpk79
Starting Member

4 Posts

Posted - 2006-09-11 : 15:17:03

I am running the query below and the count number I am getting back is not correct (I know this just be searching through manually). I get back a 0 count, when I can literally see thousands of rows that match the query. Is there any issues with doing two And's like that? I am very new to SQL, any tips appreciated.

select count(owl)
from main
where (owl =N’stopped’) and (rule is null) and (filenm is not null)

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 15:21:06

Is your Owl column NVARCHAR or NCHAR?
Is Rule column empty or really NULL?
Are you using a case sensitive collation?

select	count(owl)
from	main
where	lower(owl) = N'stopped'
	and ISNULL(rule, '') = ''
	and filenm is not null

Peter Larsson
Helsingborg, Sweden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-11 : 15:26:27

Also, check (using query analyzer) if owl has leading spaces. I've seen data entered via the enterprise manager grid with pre-pended spaces.

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 15:35:27

Run this query

SELECT	Owl,
	CASE
		WHEN LOWER(CAST(Owl AS VARCHAR(7))) = 'stopped' THEN 'lower case'
		ELSE 'not lower case'
	END OwlStatus,
	Rule,
	CASE
		WHEN Rule IS NULL THEN 'is null'
		WHEN Rule = '' THEN 'is empty'
		WHEN Rule LIKE ' %' THEN 'has pre spaces'
	END RuleStatus,
	FileNm,
	CASE
		WHEN FileNm IS NULL THEN 'is null'
		WHEN FileNm = '' THEN 'is empty'
		WHEN FileNm LIKE ' %' THEN 'has pre spaces'
	END FileNmStatus
FROM	Main

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources