3 Replies Latest reply on Nov 5, 2015 2:09 PM by tdanner

    SWQL Query works in SWQL Studio but not Online

    samwise

      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.

        • Re: SWQL Query works in SWQL Studio but not Online
          tdanner

          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
          
          
          1 of 1 people found this helpful
            • Re: SWQL Query works in SWQL Studio but not Online
              samwise

              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.

                • Re: SWQL Query works in SWQL Studio but not Online
                  tdanner

                  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