Return 0 in columns if no rows selected in Oracle-CodePudding

While I am executing a query in Oracle DB it returns 0 rows. I need to return column values as 0 if no rows are selected. Please find the attached sample query.

 select NVL(staff,0), NVL(code,0)
       from stafftable
      where staffid= 123
        and code in (112, 251)
        and closedate is not null
        and rownum<2

If no rows selected I need below result:

NVL(staff,0)      NVL(code,0)
0                 0

I have tried NVL(), coalsce() not able to get the expected result.

CodePudding user response：

I have tried NVL(), coalsce() not able to get the expected result.

Because it really didn't return any result, so we might get empty result

WE can try to use CTE with UNION ALL

one is for your filter result, another is for your default result from empty result

WITH cte AS ( 
      select staff, code
       from stafftable
      where staffid= 123
        and code in (112, 251)
        and closedate is not null
        and rownum<2
)
SELECT staff, code
FROM cte
UNION ALL 
SELECT 0 , 0 
FROM dual
WHERE NOT EXISTS (
   SELECT 1
   FROM cte 
)

CodePudding user response：

Another solution with max, assuming the values in staff are positive:

select max(staff) staff, max(code) code FROM (
       select staff, code
        from stafftable
       where staffid= 123
         and code in (112, 251)
         and closedate is not null
         and rownum<2 
UNION ALL
SELECT 0 , 0 FROM dual )