Oracle DB: Return second query if first query is empty

Question

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.

Or rather, I can't find a way to do it without violating DRY. i.e. (SELECT <joinQuery1>) UNION ALL (SELECT <joinQuery2> WHERE SELECT COUNT(<joinQuery1>) = 0) should work, but I'd use that only as a last resort. — Nelson Melendez
– Nelson Melendez, Commented Oct 19, 2012 at 19:22

Craig · Accepted Answer · 2012-10-19 19:39:36Z

8

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.

answered Oct 19, 2012 at 19:39

Craig

5,87024 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nelson Melendez Over a year ago

Beautiful! Maintenance and performance (in that order) were exactly the 2 concerns I had with doing a "re-query", but this solves them both in a very clean way. Thank you!

Alexander Tokarev · Accepted Answer · 2012-10-19 19:40:01Z

0

The answer depends very much how are you going to use results of query further. So you should either use pipelened functions, insert into GTT or return ref cursor.

At any case I would recommend you to do it in 1 SQL statement to achieve read consistency.

So please consider something like

create procedure test (pCursor out sys_refcursor) is
begin
 open pCursor for
 select <joinQuery1>
 union all
 SELECT <joinQuery2>
 where not exists (select 1 from joinquery1)
 ;
end;

answered Oct 19, 2012 at 19:40

Alexander Tokarev

1,0559 silver badges16 bronze badges

1 Comment

Nelson Melendez Over a year ago

That was my initial thought as well, but from a maintenance standpoint, I'd then have to manage <joinQuery1> in 2 places. The WITH clause Craig mentioned above takes care of this very nicely.

Collectives™ on Stack Overflow

Oracle DB: Return second query if first query is empty

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related