SWQL - Combing two Left Join tables into one

Hi all,

Wondering if someone can help me out this this SWQL command, how do I combine the two status columns and merge into one?

SELECT TOP 10 [Alerts].AlertObjects.EntityCaption, [Alerts].TriggeredMessage, TOLOCAL([Alerts].TriggeredDateTime) AS Alerttime, [Interface].Status, [Nodes].Status

FROM Orion.AlertActive AS [Alerts]

LEFT JOIN Orion.NPM.Interfaces AS [Interface]

ON [Alerts].AlertObjects.EntityCaption = [Interface].Caption

LEFT JOIN Orion.Nodes AS [Nodes]

ON [Alerts].AlertObjects.EntityCaption = [Nodes].Caption

Find more posts tagged with

Accepted answers

All comments

Seashore

Did this:

SELECT TOP 1000
    [Alerts].AlertObjects.EntityCaption, 
    [Alerts].TriggeredMessage, 
    TOLOCAL([Alerts].TriggeredDateTime) AS Alerttime
    ,CASE 
       WHEN [Alerts].AlertObjects.EntityType='Orion.Nodes' THEN [Nodes].Status
       WHEN [Alerts].AlertObjects.EntityType='Orion.NPM.Interfaces' THEN [Interface].Status
       ELSE 0
    END AS [Status2]
FROM Orion.AlertActive AS [Alerts]
LEFT JOIN Orion.NPM.Interfaces AS [Interface] ON [Alerts].AlertObjects.EntityCaption = [Interface].Caption AND [Alerts].AlertObjects.EntityType='Orion.NPM.Interfaces'
LEFT JOIN Orion.Nodes AS [Nodes] ON [Alerts].AlertObjects.EntityCaption = [Nodes].Caption AND [Alerts].AlertObjects.EntityType='Orion.Nodes'

You will probably have more than two types of entities you want info from, so doing it like above enabled you to add more entity types as you go on. Also I added a "where clause" to the joins.

Molecoolar

ahh awesome thanks for that. When running the query, it returned 0 for [Nodes].Status is this because of the 'Else 0'? Also if i wanted to add another column such as Orion.Nodes.DetailsUrl would it be just adding that to the select statement?

Seashore

Change "else 0 " to "else 99" and you will see :-)

But status is only a number. We can change to "statusdescription". Then also add some code for the url:

SELECT TOP 1000
    [Alerts].AlertObjects.EntityCaption, 
    [Alerts].TriggeredMessage, 
    TOLOCAL([Alerts].TriggeredDateTime) AS Alerttime
    ,CASE 
       WHEN [Alerts].AlertObjects.EntityType='Orion.Nodes' THEN [Nodes].StatusDescription
       WHEN [Alerts].AlertObjects.EntityType='Orion.NPM.Interfaces' THEN [Interface].StatusDescription 
       ELSE '- Got no Staus -'
    END AS [Status]
    ,CASE 
       WHEN [Alerts].AlertObjects.EntityType='Orion.Nodes' THEN [Nodes].DetailsUrl
       WHEN [Alerts].AlertObjects.EntityType='Orion.NPM.Interfaces' THEN [Interface].DetailsUrl 
       ELSE '- Got no link -'
    END AS [URL]
FROM Orion.AlertActive AS [Alerts]
LEFT JOIN Orion.NPM.Interfaces AS [Interface] ON [Alerts].AlertObjects.EntityCaption = [Interface].Caption AND [Alerts].AlertObjects.EntityType='Orion.NPM.Interfaces'
LEFT JOIN Orion.Nodes AS [Nodes] ON [Alerts].AlertObjects.EntityCaption = [Nodes].Caption AND [Alerts].AlertObjects.EntityType='Orion.Nodes'