Return data from second query if first query result is empty.

This query help you to use WHEN NO_DATA_FOUND Exception feature in SQL. Here I am used three query.

1. Return result only from first query if first query return some data.
2. Return result only from Second query if first query return no data.
3. Return result only from Third query if first and second query  return no data.

Setup :

CREATE TABLE ACC_PROD
(PROD NUMBER,
 ACC NUMBER,
 RS NUMBER);

Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (1, 0, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 101, 1);
Insert into ACC_PROD
   (PROD, ACC, RS)
 Values
   (0, 0, 1);
COMMIT;

Query :

WITH DATA_FIRST

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = 0 AND ACC = :P_ACC)),

     DATA_SECOND

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = :P_PROD AND ACC = 0)),

     DATA_THIRD

     AS (SELECT *

           FROM ACC_PROD

          WHERE (PROD = 0 AND ACC = 0))

SELECT * FROM DATA_FIRST

UNION ALL

SELECT *

  FROM DATA_SECOND

 WHERE NOT EXISTS (SELECT NULL FROM DATA_FIRST)

UNION ALL

SELECT *

  FROM DATA_THIRD

 WHERE     NOT EXISTS (SELECT NULL FROM DATA_FIRST)

       AND NOT EXISTS (SELECT NULL FROM DATA_SECOND)