how to show template result only when the query returns 0 rows

user_4974086 0
Viewed 28

A query returns multiple rows in some environments but returns 0 rows in the other environments. I want to know all results in one output in the same format, so I wrote the query below. However, it works as I intended when the former part of the query returns 0 rows, but when it returns some rows, it returns an extra row. How to not show the latter query result when the former query returns rows?

SELECT
  id, name 
FROM
  members
WHERE
  some_condition_goes_here
UNION
SELECT 0 as id, 'No results' as name FROM DUAL

Actual results:

id name
0 No results
id name
1 John
6 Kate
0 No results

The last row (id = 0) is not needed in this case.

Expected results:

id name
0 No results

or

id name
1 John
6 Kate
oracle11g oracle sql
2 Answers

You can use a CTE wrapping the original query

with cte as (
    SELECT
      id, name 
    FROM
      members
    WHERE
      some_condition_goes_here
)
SELECT id, name 
FROM cte
UNION
SELECT 'no results' as id, 'no_results' as name 
FROM DUAL
WHERE not exists( select 1 from cte);
user_62199790

Here's one option; example is based on Scott's sample EMP table. It selects rows based on DEPTNO column value. There are no employees who work in department -5.

SQL> select job, ename
  2  from emp
  3  where deptno = &&par_deptno
  4  union all
  5  select 'no results', 'no results' from dual
  6  where not exists (select null
  7                    from emp
  8                    where deptno = &&par_deptno);
Enter value for par_deptno: 10

JOB        ENAME
---------- ----------
MANAGER    CLARK
PRESIDENT  KING
CLERK      MILLER

SQL> undefine par_deptno
SQL> /
Enter value for par_deptno: -5

JOB        ENAME
---------- ----------
no results no results

SQL>
user_90979060
Related