SWQL CASE Function

This is a bit of a brainmelter. Does the subquery return exactly 1 "cell". 1 row 1 column?

If no, that's your problem. If so.. not sure offhand what the issue is. The error might help.

This structure is very unfamiliar to me. I think i'd do something to try and join all the data together (maybe a jazzy right join to groups), then wrap that in a subquery to filter it down to the thing I needed. Perhaps that doesnt apply to your case, hard to tell and also you can usually do this stuff in a bunch of different ways, and mine isnt better just familiar.

YEs it does return one value.

To me it seems like the nested SELECT is not supproted inside CASE function.

i have tested this with a simple SELECT query with not luck:

, CASE

    When AO.RealEntityType= 'Orion.Groups'  then  ( SELECT  N.CustomProperties.Type_Node

FROM Orion.Nodes AS N Where N.IPAddress= '192.168.0.23' )

    ELSE AO.RelatedNodeCaption

end AS [ TEST Value]

I think none of this is "supported" as such, but this query from earlier should prove it's possible

select top 1 n.NodeID, CASE when n.nodeid = 3 then (select top 1 '1' as one from orion.nodes n) else 99 end as test from orion.nodes n

Could you supply either a bit more of the code (with some blanked values) and/or the error?

If you want to try it without the inner joins here is what I tried, still can't get the Case part going though

SELECT
[AlertHistory].DisplayName AS [Alert Name],
[AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
[AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days]
--CASE
--When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE 'a' then 'Warning'
--When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE 'b' then  'Critical'
--ELSE AlertHistory.AlertObjects.AlertConfigurations.Severity
--end AS [jjj]

FROM Orion.AlertHistory

--INNER JOIN Orion.AlertConfigurations as AC on AC.AlertID = OAH.AlertObjects.AlertConfigurations.AlertID

--WHERE EventType = 0 and  oah.AlertObjects.AlertConfigurations.AlertID IN ('714','618','735','732','941','942')  AND  OAH.TimeStamp > GetUtcDate()-30

--GROUP BY AlertConfigurations.AlertID

I think if you use the like, you need to use the % so like ‘%a’ if there are characters before but not after or like ‘a%’ if there are no characters before but after or like ‘%a%’ if there are characters before and after

Got it working 

SELECT
[AlertHistory].DisplayName AS [Alert Name],
[AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
[AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days],
CASE

When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '1' then 'Warning'
When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '2' then  'Critical'
ELSE ToString (AlertHistory.AlertObjects.AlertConfigurations.Severity)
end AS [Result]
  
FROM Orion.AlertHistory

--WHERE EventType = 0 and  [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID] IN ('714','618','735','732','941','942')  --AND  OAH.TimeStamp > GetUtcDate()-30

WHERE AlertHistory.TimeStamp > GetUtcDate()-30
GROUP BY [AlertHistory].AlertObjects.AlertID

Got it working 

SELECT
[AlertHistory].DisplayName AS [Alert Name],
[AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
[AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days],
CASE

When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '1' then 'Warning'
When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '2' then  'Critical'
ELSE ToString (AlertHistory.AlertObjects.AlertConfigurations.Severity)
end AS [Result]
  
FROM Orion.AlertHistory

--WHERE EventType = 0 and  [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID] IN ('714','618','735','732','941','942')  --AND  OAH.TimeStamp > GetUtcDate()-30

WHERE AlertHistory.TimeStamp > GetUtcDate()-30
GROUP BY [AlertHistory].AlertObjects.AlertID