-- Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
-- Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Currently, SWQL only supports the latter.
The simple CASE expression is much more efficient as the server does not need to redo the query every time. Imagine the following:
CASE
WHEN (SELECT TOP 1 cpa.CurrentValue
FROM Orion.NPM.CustomPollerAssignment cpa
Inner JOIN Orion.Nodes n2
ON cpa.NodeID = n2.NodeID
WHERE cpa.CustomPollerName = 'dm2EngineStatus' AND N2.NodeID = n.NodeID ) > 1 THEN 'Down.gif'
WHEN (SELECT TOP 1 cpa.CurrentValue
FROM Orion.NPM.CustomPollerAssignment cpa
Inner JOIN Orion.Nodes n2
ON cpa.NodeID = n2.NodeID
WHERE cpa.CustomPollerName = 'dm2EngineStatus' AND N2.NodeID = n.NodeID ) = 1 THEN 'Warning.gif'
WHEN (SELECT TOP 1 cpa.CurrentValue
FROM Orion.NPM.CustomPollerAssignment cpa
Inner JOIN Orion.Nodes n2
ON cpa.NodeID = n2.NodeID
where cpa.CustomPollerName = 'dm2EngineStatus' AND N2.NodeID = n.NodeID ) = 0 THEN 'up.gif'
ELSE 'Unknown.gif'
END AS EngineStatusLED,
could simply be changed by putting the search query as input:
CASE (SELECT TOP 1 cpa.CurrentValue
FROM Orion.NPM.CustomPollerAssignment cpa
Inner JOIN Orion.Nodes n2
ON cpa.NodeID = n2.NodeID
WHERE cpa.CustomPollerName = 'dm2EngineStatus' AND N2.NodeID = n.NodeID )
WHEN > 1 THEN 'Down.gif'
WHEN = 1 THEN 'Warning.gif'
WHEN = 0 THEN 'up.gif'
ELSE 'Unknown.gif'
END AS EngineStatusLED,
This is especially bugging me as we also cant use a sub-select value (i.e. SELECT X FROM Y as Z) from the same main query as an input. I have a use case where for a single value, I have 62 CASE possibilities to match (and that's only for a single column)...
There are two types of SQL CASE statements:
Currently, SWQL only supports the latter.
The simple CASE expression is much more efficient as the server does not need to redo the query every time. Imagine the following:
could simply be changed by putting the search query as input:
This is especially bugging me as we also cant use a sub-select value (i.e. SELECT X FROM Y as Z) from the same main query as an input. I have a use case where for a single value, I have 62 CASE possibilities to match (and that's only for a single column)...