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
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.