Oracle DB: Return second query if first query is empty

oracle, plsql, sql-server, stored-procedures

Solution

You can utilize WITH to make this perform better (and easier to maintain):

WITH query1 as (
    select 1, 2
    from dual
    where 1=0
    connect by level <= 10
),
query2 as (
    select 3, 4
    from dual
    connect by level <= 10
)
select *
from query1
union all
select *
from query2
where not exists (
    select null
    from query1
);

As is this should return the 10 rows from query2. If you remove the where 1=0 from query1 (causing it to actually return rows), you should get the 10 rows from query1.

Problem

I am writing an Oracle stored procedure to return the results of a database query. If the query does not produce any results, a second query must be run in its place. In SQL Server, I can accomplish this using something similar to the following: ``` INSERT INTO @TableVar SELECT <joinQuery1>; IF (SELECT COUNT(*) FROM @TableVar) > 0 BEGIN SELECT * FROM @TableVar; -- returns <joinQuery1> END ELSE SELECT <joinQuery2>; --returns <joinQuery2> END ``` However, I can not wrap my head around how to accomplish the same task in Oracle.

Original source