FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE QueueTable (msg_state VARCHAR2(10), enq_time DATE);

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

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

Q_NAME	Q_STATUS	STATUS
QueueName	Nothing to process Queue is empty	Nothing to process on Queue

Q_NAME	Q_STATUS	STATUS
QueueName	Nothing to process Queue is empty	Nothing to process on Queue