cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

CTE WITH clause is not working

Jump to solution

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="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
    <a:Action s:mustUnderstand="1">http://schemas.solarwinds.com/2007/08/informationservice/InformationService/QueryXml</a:Action>
    <a:MessageID>urn:uuid:a45b57ed-7116-46ab-aed7-ff4f05792c45</a:MessageID>
    <a:ReplyTo>
      <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
    </a:ReplyTo>
    <i18n xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <Culture>en-US</Culture>
    </i18n>
    <SwisSettings xmlns="http://schemas.solarwinds.com/2007/08/informationservice" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <AppendErrors>true</AppendErrors>
      <ApplicationTag>SWQL Studio</ApplicationTag>
      <DataProviderTimeout>PT30S</DataProviderTimeout>
    </SwisSettings>
    <SwisProtocolVersion xmlns="http://schemas.solarwinds.com/2007/08/informationservice">2</SwisProtocolVersion>
    <IsBase64EncodingAccepted xmlns="http://schemas.solarwinds.com/2007/08/informationservice">true</IsBase64EncodingAccepted>
    <a:To s:mustUnderstand="1">net.tcp://localhost:17777/SolarWinds/InformationService/v3/Orion/ssl</a:To>
    <o:Security s:mustUnderstand="1" xmlns:o="http://docs.oasis-open.org/wss/2004/01/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="http://schemas.xmlsoap.org/ws/2005/02/sc">
        <c:Identifier>urn:uuid:f6ec8c11-f691-43b6-9780-e6f562ca6465</c:Identifier>
      </c:SecurityContextToken>
      <Signature xmlns="http://www.w3.org/2000/09/xmldsig#">
        <SignedInfo>
          <CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
          </CanonicalizationMethod>
          <SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#hmac-sha1">
          </SignatureMethod>
          <Reference URI="#_0">
            <Transforms>
              <Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#">
              </Transform>
            </Transforms>
            <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1">
            </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="http://schemas.solarwinds.com/2007/08/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.

0 Kudos
1 Solution

SWQL doesn't support CTE, all of the examples you posted are plain SQL.

- Marc Netterfield, Github

View solution in original post

2 Replies

SWQL doesn't support CTE, all of the examples you posted are plain SQL.

- Marc Netterfield, Github

View solution in original post

Thanks for the clarification mesverrum​.  Everywhere I see that CTE is supported my mind has been substituting SWQL for SQL.

0 Kudos