Could someone add CTEs or Subqueries to the SWQL functions github documentation page? (also aliases)

They work in some specific ways in the select statement, and in the from statement at least, and seem to be close-to-required for modern dashboards.

If you're asking a DBA for the way they'd write the SQL to solve a problem, the WITH they'd normaly use doesn't work, and it looks as if there's no option for this, which makes conversations on this topic quite confusing.

ALSO

Aliases! Everyone uses them, not referenced in there at all, again no idea how you'd write stuff without it honestly. Had a hard time some years back trying to figure out what exactly this is:

FROM Orion.nodes n

-This should have a heading as without it you dont get the same behavior from the SWQL studio UI.

If you've got any other SQL-ey features knocking about in there it'd be good to know about

Find more posts tagged with

cte

alias

github

SWQL

Accepted answers

All comments

KMSigma.SWI

CTE's aren't supported - nor is WITH or TempTables because what you are querying aren't technically tables (as we think of them). Insofar as subqueries, they pretty much work as you'd think (if you know T-SQL). Here's an example that I posted elsewhere.

SELECT ROUND(((DownCount / TotalNodes) * 100), 2) AS [DownPercent]
FROM (
      SELECT SUM(CASE [Nodes].Status
                        WHEN 2
                              THEN 1.0
                        ELSE 0
                        END) AS [DownCount]
            ,COUNT([Nodes].Caption) AS [TotalNodes]
      FROM Orion.Nodes AS [Nodes]
      )

Your comments are one of the reasons I don't shortcut my queries. If there's an alias, I always use "AS" and I comment the [EXPLETIVE DELETED] out of my queries when I feel they aren't self-explanatory.

adam.beedell

Hi Kevin,

I still think we need an addition to the page, here's my logic:

1) AS isn't listed either

2) the FROM-Based subquery has no description or language from the documentation page - How would someone search for it? Where in a script is it supported?

They're both in there and both work, I use em regularly, but not being able to refer to them is a roadbump to understanding

KMSigma.SWI

You are right - I skipped some of my own best practices.

SELECT ROUND((([PercentDown].DownCount / [PercentDown].TotalNodes) * 100), 2) AS [DownPercent]
FROM (
     SELECT SUM(CASE [Nodes].STATUS
                    WHEN 2
                         THEN 1.0
                    ELSE 0
                    END) AS [DownCount]
          , COUNT([Nodes].Caption) AS [TotalNodes]
     FROM Orion.Nodes AS [Nodes]
     ) AS [PercentDown]

I've got some of my own best practices and I talk about them in a few SolarWinds Lab Episodes (#85, #86, #91, and #93)

adam.beedell

Followup question - In my SWQL studio if i use "AS" rather than aliasing or using lowercase "as" i lose the autocomplete dialog

vs

This is odd, and kinda discourages doing it the proper way. Or i've got a bug, not sure. Thoughts?

(Note: "AS nodes" etc being the default format for the top line for SWQL alerts makes this extra fiddly)