I am trying to monitor a queues on a dashboard.
I want my select to return something even if the queue table is empty, look at my current qry.
SELECT 'QueueName' Q_NAME,
CASE
WHEN COUNT(*) = 0
THEN (SELECT 'Nothing to process Queue is empty' FROM DUAL)
ELSE 'something in the Queue'
END Q_STATUS,
CASE
WHEN MSG_STATE = 'READY' AND MIN(ENQ_TIME) > SYSDATE - 5/(24*60)
THEN 'OK: processing'
ELSE 'Nothing to process on Queue'
END STATUS
FROM QueueTable
GROUP BY MSG_STATE;
New contributor
ntsikayezwe Ndala is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
If you question is:
How can I return a row when the table is empty?
Then you can use UNION ALL to add another row with the defaults for an empty table and then order the rows by priority, with Queue detail rows having higher priority that the default row, and output only the highest priority rows:
SELECT Q_Name, Q_Status, Status
FROM (
SELECT 'QueueName' AS Q_NAME,
CASE
WHEN COUNT(*) = 0
THEN 'Nothing to process Queue is empty'
ELSE 'something in the Queue'
END AS Q_STATUS,
CASE
WHEN MSG_STATE = 'READY' AND MIN(ENQ_TIME) > SYSDATE - 5/(24*60)
THEN 'OK: processing'
ELSE 'Nothing to process on Queue'
END AS STATUS,
1 AS priority
FROM QueueTable
GROUP BY MSG_STATE
UNION ALL
SELECT 'QueueName',
'Nothing to process Queue is empty',
'Nothing to process on Queue',
2
FROM DUAL
)
ORDER BY priority
FETCH FIRST ROW WITH TIES;
Which, for the sample empty table:
CREATE TABLE QueueTable (msg_state VARCHAR2(10), enq_time DATE);
Outputs:
| Q_NAME | Q_STATUS | STATUS |
|---|---|---|
| QueueName | Nothing to process Queue is empty | Nothing to process on Queue |
Note: When using a GROUP BY clause, COUNT(*) = 0 will never be true as you cannot have a group with zero rows (as the group does not exist).
So your query can be simplified to:
SELECT Q_Name, Q_Status, Status
FROM (
SELECT 'QueueName' AS Q_NAME,
'something in the Queue' AS Q_STATUS,
CASE
WHEN MSG_STATE = 'READY'
AND MIN(ENQ_TIME) > SYSDATE - INTERVAL '5' MINUTE
THEN 'OK: processing'
ELSE 'Nothing to process on Queue'
END AS STATUS,
1 AS priority
FROM QueueTable
GROUP BY MSG_STATE
UNION ALL
SELECT 'QueueName',
'Nothing to process Queue is empty',
'Nothing to process on Queue',
2
FROM DUAL
)
ORDER BY priority
FETCH FIRST ROW WITH TIES;
fiddle