This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL Query works in SWQL Studio but not Online

samwise over 8 years ago

I'm fairly new to SWQL and SQL but was able to build this query in SWQL Studio in preparation to moving it over to the web report builder but when I copied it over into the web report builder I get an error stating that it is an invalid query.

Following is the query:

select n.dns,

(select count(p.Speed) as sp1

from orion.udt.port p

where p.nodeid=n.nodeid

and p.speed = 10000000000) as Port_Total,

(select count(p.Speed) as sp2

from orion.udt.port p

where p.nodeid=n.nodeid

and p.speed = 10000000000

and p.operationalstatus=1) as Port_Active

from orion.nodes n

where n.dns like 'r1-%'

or n.dns like 'r2-%'

or n.dns like 'wr-%'

or n.dns like 'cr-%'

order by n.dns

Any help on this would be much appreciated.

Top Replies

tdanner over 8 years ago +1

The Report Builder makes some transformations to a query to deal with paging and contextual filters that might be applied to a report. It looks like these transformations are getting confused by your subqueries…

Parents

0 tdanner over 8 years ago

The Report Builder makes some transformations to a query to deal with paging and contextual filters that might be applied to a report. It looks like these transformations are getting confused by your subqueries in the SELECT clause. Fortunately it is straightforward to rewrite your query to use joins instead of subqueries. Like this:

SELECT n.DNS, COUNT(sp1.Speed) AS Port_Total, COUNT(sp2.Speed) AS Port_Active
FROM Orion.Nodes n
LEFT JOIN Orion.UDT.Port sp1 ON n.NodeID=sp1.NodeID AND sp1.Speed=10000000000
LEFT JOIN Orion.UDT.Port sp2 ON n.NodeID=sp2.NodeID AND sp2.Speed=10000000000 AND sp2.OperationalStatus=1
WHERE n.DNS LIKE 'r1-%' OR n.DNS LIKE 'r2-%' OR n.DNS LIKE 'wr-%' OR n.DNS LIKE 'cr-%'
GROUP BY n.DNS
ORDER BY n.DNS

0 samwise over 8 years ago in reply to tdanner

First off, thank you so much for your quick response. I tried the new query and it did return results but I'm now getting identical values for both the Port_Active and Port_Total columns where they should be different in the majority of the records returned.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 samwise over 8 years ago in reply to tdanner

First off, thank you so much for your quick response. I tried the new query and it did return results but I'm now getting identical values for both the Port_Active and Port_Total columns where they should be different in the majority of the records returned.
Cancel
Vote Up 0 Vote Down

Cancel

Children

0 tdanner over 8 years ago in reply to samwise

You are right. I should have looked at the results more closely before I posted the query! I got the join logic wrong. Let's do it a different way:

SELECT n.DNS, COUNT(sp1.Speed) AS Port_Total, COUNT(CASE sp2.OperationalStatus WHEN 1 THEN 1 ELSE NULL END) AS Port_Active  
FROM Orion.Nodes n  
LEFT JOIN Orion.UDT.Port sp1 ON n.NodeID=sp1.NodeID AND sp1.Speed=10000000000  
WHERE n.DNS LIKE 'r1-%' OR n.DNS LIKE 'r2-%' OR n.DNS LIKE 'wr-%' OR n.DNS LIKE 'cr-%'  
GROUP BY n.DNS  
ORDER BY n.DNS