Did SWQL subqueries break in npm 12.2?

Question

All,

Did SWQL subqueries change in NPM 12.2? I recently upgraded and noticed I had some reports not working. This looks for sites that dont have some devices in them. But its not returning the List of sites that it should:

Select NC.City

FROM Orion.NodesCustomProperties NC

WHERE City NOT IN (

SELECT

NC.City

FROM Orion.Nodes

INNER JOIN Orion.NodesCustomProperties NC ON (Nodes.NodeID = NC.NodeID)

WHERE (Nodes.MachineType LIKE '%36%')

AND (Nodes.Status <> '2')

)

tom.rybka · Accepted Answer

I have tried to play with the query reported, executed the subquery in "NOT IN" operand as well as the whole query, inspected its processing and I have not found any suspicious behavior. The queries are sent to SQL directly in semantically equivalent form. Not really sure what the issue is. I also haven't found any difference in a query processing in NPM 12.2.

You say that the query is supposed to return a list of sites that don't have "some" devices in them. The subquery obviously returns a list of cities that have devices meeting criteria in the WHERE clause. The whole query then actually makes a diff between all cities present and cities with the "some" devices. Please excuse my question, but are you sure that there were any other cities left that were not covered by a subquery? (The criteria is also volatile due to the Status property test.)