SWQL CASE Function

is it possible to use SELECT statement after then when use CASE function ?

THEN SELECT SWQL CODE

THEN (SELECT SWQL CODE)

i tested both examples but it seem that it's not working i get There was an error processing the request.

Yes, though i've never had to

Hey Adam thanks for answering, im facing an error while processing this request.

i need to retreive a Custom Property when the alert is based on agroup. The whole SWQL works fine until i add this one:

, CASE
    When AO.RealEntityType= 'Orion.Groups'  then  ( SELECT  N.CustomProperties.Type_Node   FROM Orion.Nodes AS N LEFT JOIN Orion.ContainerMemberSnapshots AS CMS ON N.NodeID=CMS.EntityID LEFT JOIN Orion.Container AS C ON C.ContainerID=CMS.ContainerID WHERE C.Name = 'GRP_APLM' )
    ELSE AO.RelatedNodeCaption
end AS [ TEST Value]

when i test the SELECT part alone in a custom query widget i retreive the value i'm looking for , but the moment i nest this code between () i get an error.

thanks in advance for your time and help.

Hey Adam thanks for answering, im facing an error while processing this request.

i need to retreive a Custom Property when the alert is based on agroup. The whole SWQL works fine until i add this one:

, CASE
    When AO.RealEntityType= 'Orion.Groups'  then  ( SELECT  N.CustomProperties.Type_Node   FROM Orion.Nodes AS N LEFT JOIN Orion.ContainerMemberSnapshots AS CMS ON N.NodeID=CMS.EntityID LEFT JOIN Orion.Container AS C ON C.ContainerID=CMS.ContainerID WHERE C.Name = 'GRP_APLM' )
    ELSE AO.RelatedNodeCaption
end AS [ TEST Value]

when i test the SELECT part alone in a custom query widget i retreive the value i'm looking for , but the moment i nest this code between () i get an error.

thanks in advance for your time and help.

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?