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.

CTE WITH clause is not working

I am trying to write an SWQL query that uses a CTE WITH clause.  I know it should be possible as I've found many examples on Thwack showing that it's possible.  For the life of me, I cannot get any of the examples to work in either SWQL Studio or the web-based Report Writer.  When I run an example I get the errors "mismatched input 'WITH' expecting 'SELECT'" and "* Query is not valid", respectively.

Thwack examples

Custom SQL Report - Top Alerts, Top Offender

SQL Report Queries - can use CTEs?

Using Common Table Expressions (CTE) in SQL Reports

My test environment

Windows Server 2019 Datacenter Evaluation

VirtualBox

Orion Platform HF1, NPM: 2019.4 (Evaluation)

SWQL Studio 2.6.0.227

I have come up with a trivial example.  In this query, I am selecting from a subquery instead of a table.  This works but does not use WITH.

SELECT
    AllNodes.DisplayName
FROM
    (
        SELECT
            data.Caption AS DisplayName
        FROM
            orion.nodes AS data ) AS AllNodes;


Which returns using SWQL Studio

DisplayName
vagrant

I then rewrite the example using WITH. And yes, it starts with a semicolon.

;WITH
    AllNodes
    (
        DisplayName
    ) AS
    (
        SELECT
            data.Caption AS DisplayName
        FROM
            orion.nodes As data
    )
SELECT
    AllNodes.DisplayName
FROM
    AllNodes;

SWQL Studio displays

Screen Shot 2019-11-26 at 3.38.49 PM.png

and logs

2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception running query:
;WITH
    AllNodes
    (
        DisplayName
    ) AS
    (
        SELECT
            data.Caption AS DisplayName
        FROM
            orion.nodes AS data
    )
SELECT
    AllNodes.DisplayName
FROM
    AllNodes;
RETURN XML RAW
2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception for Operation: <s:Envelope xmlns:s="">www.w3.org/.../soap-envelope" xmlns:a="">www.w3.org/.../addressing" xmlns:u="">docs.oasis-open.org/.../oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
    <a:Action s:mustUnderstand="1">schemas.solarwinds.com/.../a:Action>
    <a:MessageID>urn:uuid:a45b57ed-7116-46ab-aed7-ff4f05792c45</a:MessageID>
    <a:ReplyTo>
      <a:Address>www.w3.org/.../a:Address>
    </a:ReplyTo>
    <i18n xmlns="">schemas.solarwinds.com/.../informationservice" xmlns:i="">www.w3.org/.../XMLSchema-instance">
      <Culture>en-US</Culture>
    </i18n>
    <SwisSettings xmlns="">schemas.solarwinds.com/.../informationservice" xmlns:i="">www.w3.org/.../XMLSchema-instance">
      <AppendErrors>true</AppendErrors>
      <ApplicationTag>SWQL Studio</ApplicationTag>
      <DataProviderTimeout>PT30S</DataProviderTimeout>
    </SwisSettings>
    <SwisProtocolVersion xmlns="">schemas.solarwinds.com/.../SwisProtocolVersion>
    <IsBase64EncodingAccepted xmlns="">schemas.solarwinds.com/.../IsBase64EncodingAccepted>
    <a:To s:mustUnderstand="1">net.tcp://localhost:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>
    <o:Security s:mustUnderstand="1" xmlns:o="">docs.oasis-open.org/.../oasis-200401-wss-wssecurity-secext-1.0.xsd">
      <u:Timestamp u:Id="_0">
        <u:Created>2019-11-26T22:38:25.660Z</u:Created>
        <u:Expires>2019-11-26T22:43:25.660Z</u:Expires>
      </u:Timestamp>
      <c:SecurityContextToken u:Id="uuid-5a226932-69d5-479e-b174-b4b9a6f1a6be-4" xmlns:c="">schemas.xmlsoap.org/.../sc">
        <c:Identifier>urn:uuid:f6ec8c11-f691-43b6-9780-e6f562ca6465</c:Identifier>
      </c:SecurityContextToken>
      <Signature xmlns="">www.w3.org/.../xmldsig
        <SignedInfo>
          <CanonicalizationMethod Algorithm="">www.w3.org/.../xml-exc-c14n
          </CanonicalizationMethod>
          <SignatureMethod Algorithm="">www.w3.org/.../xmldsig
          </SignatureMethod>
          <Reference URI="#_0">
            <Transforms>
              <Transform Algorithm="">www.w3.org/.../xml-exc-c14n
              </Transform>
            </Transforms>
            <DigestMethod Algorithm="">www.w3.org/.../xmldsig
            </DigestMethod>
            <DigestValue>uLT+lm8roVZpwyq79tSa+z8sVyU=</DigestValue>
          </Reference>
        </SignedInfo>
        <SignatureValue>0Sgq/gDRbF/UP13VtIUAWaN5Qog=</SignatureValue>
        <KeyInfo>
          <o:SecurityTokenReference>
            <o:Reference URI="#uuid-5a226932-69d5-479e-b174-b4b9a6f1a6be-4">
            </o:Reference>
          </o:SecurityTokenReference>
        </KeyInfo>
      </Signature>
    </o:Security>
  </s:Header>
  <s:Body>
    <QueryXml xmlns="">schemas.solarwinds.com/.../informationservice">
      <query>;WITH
    AllNodes
    (
        DisplayName
    ) AS
    (
        SELECT
            data.Caption AS DisplayName
        FROM
            orion.nodes AS data
    )
SELECT
    AllNodes.DisplayName
FROM
    AllNodes;
RETURN XML RAW</query>
      <parameters>
      </parameters>
    </QueryXml>
  </s:Body>
</s:Envelope>
2019-11-26 14:38:25,674 [72] ERROR SolarWinds.InformationService.Core.InformationService - SWQL Studio (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery
SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input 'WITH' expecting 'SELECT' ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.
   at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)
   at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)
   at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)
   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
   --- End of inner exception stack trace ---
   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectFrom()
   at SolarWinds.Data.SWQL.Parser.SWQLParser.queryRule()
   at SolarWinds.Data.SWQL.Parser.SWQLParser.selectStatement()
   at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()
   at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)
   at SolarWinds.Data.Query.Engine.QueryProcessor.CompileQueryExecutionPlan(String query, IQueryExecutionContext context)
   at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)
   at SolarWinds.InformationService.Core.InformationService.GetQueryExecutionPlan(String query, IIdentity currentIdentity, Boolean federationEnabled, IQueryExecutionContext queryExecutionContext)
   at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer, IMessageFormatterStrategy messageFormatterStrategy)

I have also created an SQL Fiddle on https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b7b7773b50eedaa07ef0ebdaf6960597 to show that my examples are valid SQL and produce the same output.

Can anyone shed light on what I am doing wrong?

Message was edited by: Gary Diamond Added CTE to the title. Updated subqueries to select data.Caption instead of data.displayName. Added anchor "data" to "node" in subquery. Updated the fiddle with the same changes.