SQL*Plus (MOSC)

MOSC Banner

Return a row with 0s when query results in no data found

in SQL*Plus (MOSC) 3 commentsAnswered ✓

Hello,

This is on Oracle DB Extreme Performance 19c.

I have following query that has two sub queries in the with clause, if either of this query or both returns no data found, then instead of returning no rows, I need to return one row for each sub query returning all 0s in all columns.

Please advise how do I achieve in this sql statement.

WITH rws AS (
 SELECT 'LINEbizGLNRTMSFTSErvice-XXLIN_GL_NRT_STG_QTAB' service_queue,decode(state,0,'READY',1,'WAITING',2, 'PROCESSED', 3, 'EXPIRED') state
FROM xxlin_gl_nrt_stg_qtab
WHERE enq_time >= (sysdate-16/24)
UNION ALL
 SELECT 'LINEbizGLNRTMSFTSErvice-XXLIN_PROD_SYNC_STG_QTAB' service_queue,decode(state,0,'READY',1,'WAITING',2, 'PROCESSED', 3, 'EXPIRED') state
FROM xxlin_prod_sync_stg_qtab
WHERE enq_time >= (sysdate-16/24)
)
 SELECT * FROM rws
 PIVOT (COUNT(*)
   FOR state IN (
   'READY', 'WAITING', 'PROCESSED', 'EXPIRED'
  )
 );

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center