Oracle DB - If result of Query1 is empty try Query2

Question

I have 'inherited' a database application (based on an Oracle 10g database and written in Delphi), which has to make several decisions as follows:

Do Query1.
If the result set is empty, do Query2.

The queries are SELECT queries having the same output columns and sometimes similar (Case 1) and sometimes different (Case 2) input (FROM) and conditions (WHERE).

Case 1: Sometimes the difference is: col like 'x' (or col = 'x') becomes col like '%'.

Performance: it probably would be best to use two different queries (col = 'x' and the other query without it).
Maintainability: col like :param is better as it does not need 2 queries
Compromise solution: AND (col = 'x' OR :param = 1)

Case 2: Another time I have to use a completly different Query2 if there is no data in the resultset of Query1.

From searching stackoverflow and the internet I am aware of several solutions to my problem:

Using two SQL queries: Let the program do the work instead of the database (but needs to query the database twice instead of only once)
```
result = executeQuery(Query1)
if (isEmpty(result))
result = executeQuery(Query2)
```
Using UNION: Let Oracle do all the work (but needs to execute Query1 twice):
```
Query1 UNION ALL Query 2 WHERE NOT EXISTS (Query 1)
```
Using PL/SQL: Let Oracle do most of the work (but I read somewhere that the queries are compiled each time this way)
```
begin
 Query1;
exception when no_data_found then
 Query2;
end;
```

My questions are:

Are there other (better) solutions?
What would you (as a more experienced database user than me) use?
What are the (dis)advantages?
What are the caveats?

Erich Kitzmueller · Accepted Answer · 2012-08-08 11:55:05Z

1

In many cases, you can avoid the UNION by using the less strict WHERE clause, but include a ORDER BY clause so the better matching result appears first. If necessary, you can use the ROWNUM pseudocolumn to restrict the result to the first one.

For example, instead of

 BEGIN
   SELECT * INTO myrec FROM mytable WHERE xxx = 'x';
 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     SELECT * INTO myrec FROM mytable WHERE xxx like '%x%';
 END;

you could write

 SELECT * INTO myrec FROM (
   SELECT * FROM mytable WHERE X like '%x%'
     ORDER BY CASE when xxx='x' THEN 1 ELSE 2 END
 ) WHERE ROWNUM=1;

Note the nested SELECT statements; this is necessary because otherwise, the WHERE ROWNUM=1 clause would limit the result set before the ORDER BY takes place.

answered Aug 8, 2012 at 11:55

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

Oracle DB - If result of Query1 is empty try Query2

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related