SQL replase empty row to 0

Question

I am trying to get a list of locks on ORACLE. When there are no locks, an empty string is returned. How to make it output 0 if there are no rows, and output the required result if there are rows?

SELECT (b.seconds_in_wait) as TIME
FROM sys.v_$session b, sys.dba_blockers c, sys.dba_lock a
WHERE c.holding_session = a.session_id AND c.holding_session = b.sid and (username like '%MOBILE%');

I don't even know where to look for the answer)

Answer 1

Left join your sql to a dummy row and handle null with Nvl() function

WITH
    dummy AS 
        ( Select 0 "DUMMY" From Dual),
    blocked AS 
        (   -- your SQL using Joins
            SELECT  b.SECONDS_IN_WAIT "A_TIME"
            FROM    sys.v_$session b  
            INNER JOIN sys.dba_lock a ON(a.SESSION_ID = b.SID)
            INNER JOIN sys.dba_blockers c ON(c.HOLDING_SESSION = b.SID)
            WHERE   b.username LIKE('%MOBILE%')         
        )
SELECT  Nvl(b.A_TIME, 0) "A_TIME"
FROM    dummy
LEFT JOIN blocked b ON(1 = 1)