Displaying Zero Count

dalezjc · #1 (**permalink**) October 12th, 2010, 05:19 PM

So I have a checklist I've created for an app that we use, and it displays (among other things) open events, and action-errors counts. In one instance, the hyperlink and its associated count doesn't display when the count is 0. However, the other instance displays a zero count just fine. I've looked at this for a while now and my eyes are all blurry, and I can't see the obvious error.

Here's the one that works:

Code:

 
set rsqdbb = conn.Execute ("Select count (*) as scripts_no_action_total " _
& "from Job J " _
& "left join ActionTmpl A on A.OwnerJobID = J.JobID   " _
& "left join Object O on O.ObjID = J.MachineObjID  " _
& "left Join ViewHierarchy vh  " _
& "On o.ObjID = vh.ObjID  " _
& "Join object o2  " _
& "ON vh.parentobjID=o2.ObjID  " _
& "WHERE o.typeid = 2  " _
& "and J.ParentJobID is not null     " _
& "and A.ActionName is NULL    " _
& "and J.Status &0xFF =1    " _
& "and J.KSName not like '%analy%'    " _
& "and J.KSName not like '%systemuptime%'    " _
& "and J.KSName not like '%top%'    " _
& "and J.KSName not like '%discovery%'  " _
& " and (o2.Name like '%storage%' or o2.Name like '%comm%') ")
 
<%do while not rsqdbb.EOF%></do>
<a href="http://show_scripts_no_actions_storage_det0.asp" target="content">Show Jobs With No Actions
<font color=maroon>&nbsp;(<%response.write rsqdbb("scripts_no_action_Total")%>)</font><br />
<%rsqdbb.MoveNext%>
<%loop%>

And here's the one that doesn't display the hyperlink at all:

Code:

 
set rsqdbte = conn.Execute ("SELECT count (distinct EventMSG) as Total_Events" _
& " FROM Object o Join ViewHierarchy vh " _
& " On o.ObjID = vh.ObjID " _
& " Join object o2 " _
& " ON vh.parentobjID=o2.ObjID " _
& " Join event " _
& " On event.machineobjid = o.objID " _
& " WHERE o.typeid IN (2,16) " _
& " and (o2.Name like '%storage%' or o2.Name like '%comm%') " _
& " AND vh.ViewID=0 " _
& " and event.status = 0 " _
& " and eventMSG not like '%action%' " _
& " and eventMSG not like '%boot%' " _
& " and eventmsg NOT LIKE '%DISCOVERY%' " _
& " and eventmsg not like '%normal%' " _
& " and eventmsg not like '%state change%' " _
& " group by o.typeid ")
 
<%do while not rsqdbte.EOF%></do>
<a href="http://show_storage_events_det0.asp" target="content">Show Open Events
<font color=maroon>&nbsp;(<%response.write rsqdbte("Total_Events")%>)</font><br />
<%rsqdbte.MoveNext%>
<%loop%>

Thanks for any help!

Dale

Old Pedant · #2 (**permalink**) October 12th, 2010, 06:44 PM

DEBUG DEBUG DEBUG...

What happens when you execute that query from SQL Server management console (or QA if you are still using SQL Server 2000)??

This is PURELY a guess. But I think it might be because you are indeed doing

Code:

COUNT(DISTINCT eventmsg)

If there are *NO* eventmsg instances found, then you are essentially asking it to do

Code:

COUNT(DISTINCT null)

and I am just *guessing* that a COUNT(null) is itself null, so you get no records.

I'll bet you could fix this easily, if I'm right. Just do:

Code:

SELECT ISNULL( count(distinct EventMSG), 0 ) as Total_Events

dalezjc · #3 (**permalink**) October 13th, 2010, 08:53 AM

Old Pendant,

I should have clarified my post. You're right. When the count is empty (and it is sometimes), I'd like it to display (for example) Show Open Events (0). And it does this in the first bit of code, but not the second. For some odd reason, when the count is zero, in the second bit of code, the entire line does not get displayed.

Does this help?

Dale

Old Pedant · #4 (**permalink**) October 13th, 2010, 02:41 PM

You just repeated what you said in first post. My answer is the same.

Again, if the COUNT(DISTINCT xxx) is returning a NULL, then you will *NOT* get ANY records and your "do while not rsqdbte.EOF" will indeed not loop at all. No records, no results.

Of course, there is an easy way to fix this in the ASP code. Just replace this code:

Code:

<%do while not rsqdbte.EOF%>
</do>
   <a href="http://show_storage_events_det0.asp" target="content">Show Open Events 
    <font color=maroon>&nbsp;(<%response.write rsqdbte("Total_Events")%>)</font>
    <br />
<%rsqdbte.MoveNext%> 
<%loop%>

with this:

Code:

<%
... make the query ...
total = 0
If Not rsqdbte.EOF Then total = rsqdbte(0)
%>
<a href="http://show_storage_events_det0.asp" target="content">Show Open Events 
    <font color=maroon>&nbsp;(<%=total%>)</font>
    <br />
...

dalezjc · #5 (**permalink**) October 18th, 2010, 09:06 AM

Thanks OldPendant! You saved the day yet again.

Dale

Need to download code?

Navigation