IsNull - 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)

IsNull

Author

Topic

Kappy
Starting Member

30 Posts

Posted - 2002-07-17 : 16:47:21

Hi-

I'm trying to assign a value of 0(zero) to a null field, but can't get it to work correctly. I've tried to set the field to other numbers instead of 0, but nothing works... just keeps returning (null).

select tc.iOwnerID,
tc.vchEmpNum,
IsNull(COUNT(inc.iIncidentID),0)
from #tempComb tc, incident inc
where tc.iOwnerID = inc.iOwnerID
and inc.tirecordstatus = 1
and inc.dtInsertDate > '07/01/01'
and inc.iIncidentCategory = 1
group by tc.iOwnerID, tc.vchEmpNum

Thanks.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-07-17 : 16:54:17

Not entirely sure what you are asking for but this might be it

select tc.iOwnerID,
tc.vchEmpNum,
COUNT(ISNULL(inc.iIncidentID,0))
from #tempComb tc, incident inc
where tc.iOwnerID = inc.iOwnerID
and inc.tirecordstatus = 1
and inc.dtInsertDate > '07/01/01'
and inc.iIncidentCategory = 1
group by tc.iOwnerID, tc.vchEmpNum

HTH
Jasper Smith

Kappy
Starting Member

30 Posts

Posted - 2002-07-17 : 17:03:18

Well, unfortunately that didn't work.

what I'm trying to do is get some averages and when the count of that field (iIncidentID) is null, it throws off my averages. I need a count of something to be 0, not null.

is that any clearer? I've been working on this all day, and typing coherently is not my strong point right now...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-17 : 17:08:51

quote:
I need a count of something to be 0, not null

Well, the count of something is never null, even if the value being counted is null.

Have you tried this:

select tc.iOwnerID,
tc.vchEmpNum,
COUNT(*)
from #tempComb tc, incident inc
where tc.iOwnerID = inc.iOwnerID
and inc.tirecordstatus = 1
and inc.dtInsertDate > '07/01/01'
and inc.iIncidentCategory = 1
group by tc.iOwnerID, tc.vchEmpNum

Kappy
Starting Member

30 Posts

Posted - 2002-07-17 : 17:17:51

then I don't understand how the results show null

here are the results for the query I posted earlier, along with another query to get the average.

Type # emps Inc Count
------------------------- ------------------------- ----------- Client 2001-2500 3
Client 2001-2500 2
Client 2001-2500 20
Client 2001-2500 183
Client 2001-2500 17
Client 2001-2500 60
Client 2001-2500 (null)
Client 2001-2500 20
Client 2001-2500 9
Client 2001-2500 86

vchEmpNum avg
------------------------- -----------
2001-2500 44

Kappy
Starting Member

30 Posts

Posted - 2002-07-17 : 17:21:39

And how would I specify which table count(*) is for?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-07-17 : 17:41:04

I'm thinking maybe the "inc.dtInsertDate > '07/01/01'" combined with Old-Style SQL joins is causing a "outer join effect" resulting in null values maybe trying this will help.

select tc.iOwnerID, tc.vchEmpNum
-- , IsNull(COUNT(inc.iIncidentID),0)
, COUNT(inc.iIncidentID)
from #tempComb tc join incident inc on tc.iOwnerID = inc.iOwnerID
where inc.tirecordstatus = 1
and inc.dtInsertDate > '07/01/01'
and inc.iIncidentCategory = 1
group by tc.iOwnerID, tc.vchEmpNum

if you still need the IsNull function just move the comment lines.

Edited by - drymchaser on 07/17/2002 17:43:58

Kappy
Starting Member

30 Posts

Posted - 2002-07-18 : 09:27:07

unfortunately - this did not work. I tried with and without the isnull function.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-18 : 09:58:56

The last thing I can think of is to run the query twice, once with ANSI_NULLS off and again with it on:

SET ANSI_NULLS ON
...run query

SET ANSI_NULLS OFF
...run query

And see if it changes the results. I think that ANSI_NULLS on might be causing the null result.

And make sure to use the ANSI JOIN syntax as drymchaser suggested, don't do the join in the WHERE clause.

If this does not work, you'll need to post your table structures and some sample data for us to use in trying to replicate the problem.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-07-18 : 12:35:31

Another thought for your query, after this we'd need to see some of the details robvolk was requesting.

One question:

Are there multiple vchEmpNum for each iOwnerID or vice versa?

select tc.iOwnerID, tc.vchEmpNum
-- , IsNull(COUNT(inc.iIncidentID),0)
, COUNT(inc.iIncidentID)
from #tempComb tc inner join
(
select iOwnerID, iIncidentID
from incident
where tirecordstatus = 1
and dtInsertDate > '07/01/01'
and iIncidentCategory = 1
) inc on tc.iOwnerID = inc.iOwnerID
group by tc.iOwnerID, tc.vchEmpNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources