RE: T-SQL Help! -- sql server clients

Jose
1/29/2004 12:39:22 PM

Hi Baisong,
The result set for the second query shows 50 rows with a
NULL value in T1. Is there something about the way NULL
values are treated in the first query?

-Jose

[quoted text, click to view]

Jose
1/29/2004 5:21:43 PM

Hi Michael,
Please see my reply to the other post below. Am I
missing something? I would like a count of all rows
where the value in column1 is NULL. I could always
execute SELECT * FROM T1 where column1 IS NULL in Query
Analyzer and see how many rows are returned. However, all
I wish returned is a number and not a large result set.

Thanks!
-Jose

BTW I enjoy reading your editorial in SQL Server
Magazine every month! (I knew I recognized your name!)

[quoted text, click to view]

Craig Kelly
1/30/2004 4:52:03 AM

[quoted text, click to view]

Jose,

Taking your SELECT's above I create the following...

set nocount on
go

create table T1 (
column1 int null
)
go

insert T1 values (null)
insert T1 values (null)
insert T1 values (null)
insert T1 values (1)
insert T1 values (2)
insert T1 values (3)
go

set nocount off
go

SELECT COUNT(*) AS CountMe
FROM T1
WHERE (column1 IS NOT NULL)

SELECT top 50*
FROM T1
WHERE (column1 IS NOT NULL)
go

drop table T1
go

And the results I got were

CountMe
-----------
3

(1 row(s) affected)

column1
-----------
1
2
3

(3 row(s) affected)

My assumption is that you're getting results akin to

CountMe
-----------

(0 row(s) affected)

column1
-----------
1
2
3

(3 row(s) affected)

If that's the case, then I would suggest that the query with the COUNT(*)
has an error in it. Perhaps the error was disguised when you reduced the
query for posting purposes?

Craig

Jose
1/30/2004 5:29:39 PM

Hi Craig,
I ran what you wrote below and I received the same
results as you. Is there a setting on the DB or table
that is preventing me from executing this query?

Here is an augmented query:
SELECT COUNT(*) AS CountMe
FROM T1
WHERE (column1 IS NOT NULL)

However, when the following query is run, a result set is
returned:

SELECT *
FROM T1
WHERE (column1 IS NOT NULL)

Yields: (6284088 row(s) affected)

???

[quoted text, click to view]

v-baiwei NO[at]SPAM online.microsoft.com
1/31/2004 7:29:53 AM

Hi Jose,

Thank you for using the newsgroup and it is my pleasure to help you with
your issue.

I am a little confusing now. As far as I know, here is on option to prevent
judge the NULL value in the SQL Server. So, when you run the following
statements in the Query Analyzer:

SELECT COUNT(*) AS CountMe
FROM T1
WHERE (column1 IS NOT NULL)
go
SELECT *
FROM T1
WHERE (column1 IS NOT NULL)
go

based on your last reply, you will get two results windows:
In the 'Grids' tab, will show in the upper grid with the one column named
'CountMe', the content would be '6284088'; the lower grid would be 6284088
records with column1 is not NULL;
In the 'Messages' tab, will show

(1 row(s) affected)

(6284088 row(s) affected)

Is this what you got?? If yes, could you explain a bit more clearly what
you request to the results and what you want to get? Looking forward to
your reply. Thanks.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Erland Sommarskog
1/31/2004 11:43:32 PM

Jose (rcmaniac23@yahoo.com) writes:
[quoted text, click to view]

If this query returns no result set, or a an empty result set with
"(0 rows affected)", then there is something really fishy.

Is T1 a real table, or is a view?

If it is a table, have you tried running DBCC CHECKTABLE on the table?

If it is a view, what is the definition?

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server clients

sql server clients : RE: T-SQL Help!