It always returns one column and one row. When there are no records at all in the table, it returns a zero.
It will also return a zero if all the records have groupids less than 3, or if the groupids greater than three have only one error. Chad -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Shook Sent: Thursday, June 03, 2004 2:28 PM To: [EMAIL PROTECTED] Subject: RE: [SA-list] Yet another question.... But what is the result when no groupid's have any errors? Mike >>> [EMAIL PROTECTED] 06/03/04 04:16PM >>> So you want to know how many groups have at least two errors where the group ID is three or higher? Does your DB support nested queries? The key to always getting a result of some kind is to get rid of the Group By in the final query. Try this: select count(*) as groupsWithTwoErrors from (select count(groupid) as mycount, groupid from clkstatus_log where groupid > 2 and status not like 'Clock%' group by groupid having count(groupid) >= 2) as tblTwoErrors The result is the number of groupIDs with two or more errors, so just check for non-zero values. If you can't do a nested query, have your DBA create a Stored Procedure that uses a temp table to do the same thing. Chad -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Shook Sent: Thursday, June 03, 2004 1:45 PM To: [EMAIL PROTECTED] Subject: RE: [SA-list] Yet another question.... Thursday, June 03, 2004 3:35:00 PM External COM check started (W00000002,149) Thursday, June 03, 2004 3:35:00 PM External COM check (W00000002,149):ERR: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.( 3021- 0) Also the SQL I'm using: select count(groupid) as mycount, groupid from clkstatus_log where groupid > 2 and status not like 'Clock%' group by groupid order by mycount DESC This would put the error count for the groupid with highest error count in the top-left cell of the recordset. Only when there is at least one error. I then have the ODBC Com look to see that this value is always less then 2. Mike >>> [EMAIL PROTECTED] 06/03/04 03:10PM >>> What exact error do you get in the logfile for this? Dirk. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Shook Sent: Thursday, June 03, 2004 9:04 PM To: [EMAIL PROTECTED] Subject: [SA-list] Yet another question.... Well my DBA's are hard at it and have me stumped again. I'm using the ODBC Com addon to check a table and count the number of errors in it. This works great when the table actually has at least one error because the count is greater than 0. Since SQL doesn't return any rows for when the count = 0, ODBC com gives an error saying that it can't check for a down if there is no data to check. This makes perfect sense from the ODBC com's point of view, but I'm not sure how to work around the behavior of SQL. It seems like I either need a way to tell ODBC Com addon that an empty recordset is automatically UP, OR a way to force SQL to return a row displaying "0" so that ODBC Com can check agianst it. Any ideas? Michael Shook Technical Analyst Saddle Creek Corporation 723 Joe Tamplin Industrial Blvd Macon GA 31217 478 742 8740 ext. 105 (work) 478 256 9318 (mobile) 478 742 7917 (fax) [EMAIL PROTECTED] http://www.saddlecrk.com --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive --- [This E-mail scanned for viruses by Declude Virus] To unsubscribe from a list, send a mail message to [EMAIL PROTECTED] With the following in the body of the message: unsubscribe SAlive
