Return a row with 0s when query results in no data found
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' ) );