I had the same problem but was able to get it resolved with help from support. I was setting up what looks like the same kind of thing as you as I was creating a load average alert on a custom node poller. I'll throw out a couple assumptions that I think apply to you that applied to me. You have set up a Load Average custom poll in the Universal Device Poller, the SNMP Get Type under Advanced Options in the Universal Device Poller is GET TABLE, and you are using the web interface to create the alert.
Based on what you have listed in your select statement you have Custom Node Poller selected on the Trigger Condition page under Set up your SQL condition. You need to change this to Custom Node Table Poller and update your JOIN and WHERE statements accordingly.
Even though the query works when you run it through SSMS, the application appears to care that it is a GET TABLE type and wants you to use the Custom Node Table Poller condition instead of Custom Node Poller. I took a bit of a different approach on my trigger but I have included my SQL below for reference if it is helpful.
Interestingly, I have access to an older instance of Orion and it did not care that I selected Custom Node Poller. So if you have an alert that used to work but doesn't after upgrade, that may be the culprit.
SELECT CustomPollerStatusTable.FullLabel, CustomPollerStatusTable.CustomPollerAssignmentID, CustomPollerStatusTable.CompressedRowID FROM CustomPollerStatusTable
INNER JOIN Nodes ON CustomPollerStatusTable.NodeID = Nodes.NodeID
INNER JOIN CustomPollers ON CustomPollerStatusTable.PollerID = CustomPollers.PollerID
INNER JOIN NodesCustomProperties ON Nodes.NodeID = NodesCustomProperties.NodeID
CustomPollers.UniqueName = 'LoadAvg'
AND CustomPollerStatusTable.NumValue >= NodesCustomProperties.Threshold_LinuxLoadAvg15
AND CustomPollerStatusTable.RowID = 3