MySQL code- Problems with cade when inside a WHERE clause
-
The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed? /* PATCH MANAGEMENT */ DELIMITER $$ #MachineType means Server or Workstation as determined by the word server in the os field DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$ CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30), ClientName varchar(30)) BEGIN SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts' FROM (SELECT HF.computerid, HF.hotfixID FROM hotfixdata HFD INNER JOIN hotfix HF ON HF.hotfixid = HFD.hotfixid AND HFD.ignore <> 1 LEFT OUTER JOIN Computers AS C ON C.ComputerID=HF.ComputerID LEFT OUTER JOIN locations AS L ON C.LocationID=L.LocationID LEFT OUTER JOIN Clients AS CL ON C.ClientID=CL.ClientID JOIN agentcomputers AS AC ON C.ComputerID=AC.computerid JOIN mastergroups AS MG ON AC.groupid=MG.groupId #Enter Case When here seperating different where clauses. WHERE CASE WHEN Criteria = 'Workstations Missing Patches' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND HF.installed <> 1 AND HF.Approved = 1 AND LocationID = L.Name AND ClientName = Cl.Name :mad: AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations with Empty Patch Tabs' THEN HF.hotfixid IS NULL AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = Cl.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations Pending Reboots' THEN (C.Flags & 1024) = 1024 AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = C.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Total Patch Workstations' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND LocationID = L.Name AND ClientName = CL.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Servers Missing Patches' THEN INSTR(C.os,"server")>0
-
The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed? /* PATCH MANAGEMENT */ DELIMITER $$ #MachineType means Server or Workstation as determined by the word server in the os field DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$ CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30), ClientName varchar(30)) BEGIN SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts' FROM (SELECT HF.computerid, HF.hotfixID FROM hotfixdata HFD INNER JOIN hotfix HF ON HF.hotfixid = HFD.hotfixid AND HFD.ignore <> 1 LEFT OUTER JOIN Computers AS C ON C.ComputerID=HF.ComputerID LEFT OUTER JOIN locations AS L ON C.LocationID=L.LocationID LEFT OUTER JOIN Clients AS CL ON C.ClientID=CL.ClientID JOIN agentcomputers AS AC ON C.ComputerID=AC.computerid JOIN mastergroups AS MG ON AC.groupid=MG.groupId #Enter Case When here seperating different where clauses. WHERE CASE WHEN Criteria = 'Workstations Missing Patches' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND HF.installed <> 1 AND HF.Approved = 1 AND LocationID = L.Name AND ClientName = Cl.Name :mad: AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations with Empty Patch Tabs' THEN HF.hotfixid IS NULL AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = Cl.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Workstations Pending Reboots' THEN (C.Flags & 1024) = 1024 AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY)) AND INSTR(C.OS,"server")=0 AND INSTR(C.OS,"microsoft")>0 AND LocationID = L.Name AND ClientName = C.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Total Patch Workstations' THEN INSTR(C.os,"server")=0 AND INSTR(C.os,"microsoft")>0 AND LocationID = L.Name AND ClientName = CL.Name AND MasterGrp = MG.Name AND MG.ParentID =0; WHEN Criteria = 'Servers Missing Patches' THEN INSTR(C.os,"server")>0
A
case whenclause can only return values, not conditions.Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions