SQL replase empty row to 0-CodePudding

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)

CodePudding user response：

Use the below by creating a virtual row using DUAL

 WITH SUB_QUERY AS(
 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%'))
 select * FROM SUB_QUERY
 union all
 select 0 FROM DUAL 
 where NOT EXISTS (SELECT 1 FROM SUB_QUERY);

CodePudding user response：

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)

CodePudding user response：

I would simply query the dual table and outer join the wait time query to it, like so:

WITH wait_time AS
 (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%'))
SELECT NVL(wt.time, 0)
FROM   dual
       LEFT OUTER JOIN wait_time wt ON 1=1;

That way, you'll always get at least one row returned, and you're only querying the wait time query once.