SWQL Studio RIGHT JOIN not working

I can do a regular JOIN or I can explicitly type LEFT JOIN, but if I type RIGHT JOIN or RIGHT OUTER JOIN I get

"Unsupported JOIN Type "RIGHT"

Yet, in the SWQL Studio "SQL Constructs Supported" section it specifically lists RIGHT OUTER JOIN as being supported. LEFT JOIN, LEFT OUTER JOIN, and INNER JOIN do work just fine.

Anybody know why this is?

Find more posts tagged with

sql

Join

SWQL

joins

SWQL Studio

orion sdk

Accepted answers

tdanner

SWIS only supports RIGHT JOIN for queries that it can translate entirely to a single SQL Server query. One of the entities in your query (Orion.AlertObjects) is handled by code in SWIS, so it can't send the whole query to SQL Server. SWIS's in-memory query processor can't handle the RIGHT JOIN.

In general, queries that use RIGHT JOIN can be rearranged to use LEFT JOIN instead. I'll count this as a vote for implementing RIGHT JOIN support for the SWIS in-memory query processor.

All comments

tdanner

Can you post the full query?

xtraspecialj

Sure. I figured out a different way to do it (I noticed that the AlertObjects entity has a relationship with the Node entity, so really no need for a RIGHT JOIN now, but... The documentation says RIGHT OUTER JOIN should work and it doesn't, so the question is still valid I suppose). Here is the query I was trying, plus, a screenshot of the error can be found below that:

SELECTac.Name AS [Alert Name],'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + TOSTRING(ao.AlertObjectID) AS [_LinkFor_Alert Name],CASE  WHEN asev.SevConv = 'Informational' THEN '\Orion\Images\ActiveAlerts\InformationalAlert.png'  WHEN asev.SevConv = 'Warning' THEN '\Orion\Images\ActiveAlerts\Warning.png'  WHEN asev.SevConv = 'Critical' THEN '\Orion\Images\ActiveAlerts\Critical.png'  WHEN asev.SevConv = 'Serious' THEN '\Orion\Images\ActiveAlerts\Serious.png'  WHEN asev.SevConv = 'Notice' THEN '\Orion\Images\ActiveAlerts\Notice.png'  Else '\Orion\Images\ActiveAlerts\MoreActionsSelected.png'  END AS [_IconFor_Alert Name],ao.EntityCaption AS [Alert Object],ao.EntityDetailsUrl AS [_LinkFor_Alert Object],ac.ObjectType AS [Object Type],ao.RelatedNodeCaption AS [Node Name],'\NetPerfMon\images\' + n.StatusLED AS [_IconFor_Node Name],ao.RelatedNodeDetailsUrl AS [_LinkFor_Node Name],TOSTRING(ao.LastTriggeredDateTime) AS [Triggered Time],CASE  WHEN aa.Acknowledged = 1 THEN TOSTRING(aa.AcknowledgedDateTime)  ELSE 'No'END AS [Acknowledged],CASE  WHEN aa.Acknowledged = 1 THEN  '\Orion\Images\ActiveAlerts\Acknowliedged_icon16x16v1.png'  ELSE '\Orion\Images\ActiveAlerts\MoreActionsSelected.png'END AS [_IconFor_Acknowledged]FROM Orion.AlertActive aaJOIN Orion.AlertObjects ao ON aa.AlertObjectID = ao.AlertObjectIDJOIN Orion.AlertConfigurations ac ON ao.AlertID = ac.AlertIDRIGHT OUTER JOIN Orion.Nodes n ON ao.RelatedNodeID = n.NodeIDJOIN (  SELECT  ac2.AlertID,  CASE  WHEN TOSTRING(ac2.Severity) = 0 THEN 'Informational'  WHEN TOSTRING(ac2.Severity) = 1 THEN 'Warning'  WHEN TOSTRING(ac2.Severity) = 2 THEN 'Critical'  WHEN TOSTRING(ac2.Severity) = 3 THEN 'Serious'  WHEN TOSTRING(ac2.Severity) = 4 THEN 'Notice'  Else TOSTRING(ac2.Severity)  END AS SevConv  FROM Orion.AlertConfigurations ac2  ) AS asev ON ac.AlertID = asev.AlertIDWHERE ac.Name LIKE '%Tier 4%'

RIGHT JOIN gives the same error. See the screenshot here for the error:

xtraspecialj

You get a chance to check the query out and see if there was something wrong with it, or is there an issue in SWQL?

tdanner

In general, queries that use RIGHT JOIN can be rearranged to use LEFT JOIN instead. I'll count this as a vote for implementing RIGHT JOIN support for the SWIS in-memory query processor.

xtraspecialj

Awesome. There was one time I ran it and instead of getting the error message above I got the other error it gets when you run too big of a query and it times out. So that may explain it. I couldn't figure out how to re-write that to be a left join at the time but now I'm thinking it could be done by joining AlertObjects to Nodes instead of Nodes to AlertObjects. That would mean I would already have had to define Nodes earlier in the FROM statement, right? So the FROM statement would need to start with:

FROM Orion.Nodes nLEFT OUTER JOIN Orion.AlertObjects ao ON n.NodeID = ao.RelatedNodeID

Correct?

Thank god for entity relationships though. I don't always use them just because they require a lot of extra typing in the SELECT statements (since you can't alias relationships), but they are great for one-offs like this.

Thanks for your help sir.