Click here to Skip to main content
12,630,940 members (31,131 online)
Rate this:
 
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 7:27am
SQL Ed359
Updated 27-Dec-12 7:29am
v2
Comments
Zoltán Zörgő 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161205.3 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2016
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