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
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
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 545
1 OriginalGriff 498
2 sanket saxena 330
3 Abhinav S 280
4 thatraja 275
0 Sergey Alexandrovich Kryukov 8,372
1 OriginalGriff 4,830
2 Peter Leow 3,784
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


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