SWQL Query to Return a String if No Row Matches Are Found

I need to alert when WPM returns particular status codes and this works just fine, but when no matches are found the SWQL query doesn't resolve and just outputs itself into the alert text. When the query doesn't find any results I want it to display 'All is Well'. I've tried using CASE and IS NOT NULL, but rather than returning the text, it just returns nothing.

What am I doing wrong here? I guess returning no rows isn't the same as returning NULL, but I don't know how to get around it.

SELECT
CASE
WHEN StatusStep IS NOT NULL
THEN StatusStep
ELSE 'All is well'
END AS StatusString
FROM
(
SELECT CONCAT ('StepId:', TransactionStepId, ' - StepReqId:',TransactionStepRequestId, ' - StatusCode: ', StatusCode, ' - URL: ', Url ) AS StatusStep
FROM Orion.SEUM.TransactionStepRequests
WHERE TransactionStepId IN (185,186,187)
AND (StatusCode like '4%' or StatusCode like '3%')
)

Find more posts tagged with

swql query

orion

SWQL

Accepted answers

All comments

cpund

Have you tried using ISNULL instead of the CASE statement?

I don't have WPM so I don't have any SEUM data, but I think it should be something like this:

SELECT ISNULL(StatusStep, '') as StatusStep
FROM
(
SELECT CONCAT ('StepId:', TransactionStepId, ' - StepReqId:',TransactionStepRequestId, ' - StatusCode: ', StatusCode, ' - URL: ', Url ) AS StatusStep
FROM Orion.SEUM.TransactionStepRequests
WHERE TransactionStepId IN (185,186,187)
AND (StatusCode like '4%' or StatusCode like '3%')
)

BaldFeegle

Thanks for that, unfortunately it doesn't return anything when there are no matches in the data. I modified the select statement to read "SELECT ISNULL(StatusStep, 'No data') as StatusStep" so it should output "No data" if there are no matches.

ecmel

You can join your original query with another table that always contain a single row. Then you can use ISNULL check for your column. You can try the example below:

SELECT
ISNULL(StatusStep, 'All is well') as StatusString
FROM
Orion.Engines E
LEFT JOIN 
(
SELECT CONCAT ('StepId:', TransactionStepId, ' - StepReqId:',TransactionStepRequestId, ' - StatusCode: ', StatusCode, ' - URL: ', Url ) AS StatusStep
FROM Orion.SEUM.TransactionStepRequests
WHERE TransactionStepId IN (185,186,187)
AND (StatusCode like '4%' or StatusCode like '3%')
) S ON 1=1
WHERE ServerType='Primary'