Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL
I am using MySql with Workbench
 
I have not been able to use the MAX(C.LastContact) agregate in the WHERE clause which is needed to get the correct results. I have tried using HAVING and CASE WHEN. Neither has worked. If I could bring in one more column into the select statement that could be used for filtering , it would work but if I do that it messes up the COUNT returning a SINGLE ROW / SINGLE NUMBER, needed for a Digital gadget. I have not been able to get it to work. I am using Dynamic SQL. Below is a portion of the code:
DOES ANYONE KNOW HOW THIS CAN BE DONE?
 
DELIMITER $$
 
DROP PROCEDURE IF EXISTS usp_cbi_TEST_LocationMonitorCounts$$
CREATE PROCEDURE usp_cbi_TEST_LocationMonitorCounts(
 
	QueryType varchar(30),
	MonitorStatus varchar(50),
	StatusTimeToFail int #In Minutes
)
BEGIN
IF QueryType LIKE ('%Count%')
THEN
		SET SelectStmt = 
 
		"SELECT COUNT(distinct(C.LocationID))AS 'Total Locations'
				
		FROM    Computers AS C
#******************************************************************************
IF ((StatusTimeToFail IS NULL) OR (StatusTimeToFail ='') OR (StatusTimeToFail =0))
THEN
	SET @TimeToFail = 15; #DEFAULT minutes
ELSE	
	SET @TimeToFail = StatusTimeToFail;
END IF;
#******************************************************************************
IF (QueryType LIKE ('%Count%'))
THEN
	IF (MonitorStatus != 'all')
	THEN
		IF (MonitorStatus LIKE('%Fail%'))
		THEN
				SET WhereClause = CONCAT(' WHERE ');
				SET WhereClause = CONCAT(WhereClause,
 " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",   '>=', @TimeToFail);
 

		ELSEIF (MonitorStatus LIKE('%OK%'))
		then
				SET WhereClause = CONCAT(' WHERE ');				
				SET WhereClause = CONCAT(WhereClause,
 " IFNULL(TIMESTAMPDIFF(minute, C.LastContact, NOW()),0) ",  '<', @TimeToFail);
Posted 27-Dec-12 6:27am
SQL Ed359
Edited 27-Dec-12 6:29am
36.3K
v2
Comments
Zoltán Zörgő at 27-Dec-12 13:19pm
   
you can use aggregate in a where clause as subquery. Please try to formulate in English the query you want. It would be better, than trying to reverse engineer from your code :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 766
1 OriginalGriff 345
2 CPallini 200
3 PIEBALDconsult 150
4 Magic Wonder 131
0 OriginalGriff 5,795
1 Sergey Alexandrovich Kryukov 5,028
2 CPallini 4,700
3 George Jonsson 3,142
4 Gihan Liyanage 2,450


Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100