Searching through tables for a value

I have a PL/SQL stored procedure which searches for a row in 3 different tables (not all columns are the same).

The logic goes as follows:

Search for the item in the first table
If item was found
select the details of it from the first table
If item was not found
search for the item in the second table
If Item was found
Select the details of it from the second table
If item was not found
Select the item from the third table (returning blank if it's not found here neither)


Here's the actual code (actual symbol names modified for security):

`PROCEDURE GETREQUEST(REQUESTID IN NUMBER, request OUT pkg_request.refcur)
IS
requestFound NUMBER := 0;
BEGIN

-- Search for the request ID in the first table
SELECT COUNT(REQ_ID) INTO requestFound FROM FIRSTTABLE
WHERE FIRSTTABLE.REQ_ID = REQUESTID
AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A');

IF(REQUESTFOUND > 0) THEN
-- Select the request details
OPEN REQUEST FOR
SELECT REQ_ID, REQ_TYPE_STATUS FROM FIRSTTABLE
WHERE FIRSTTABLE.REQ_ID = REQUESTID
AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A');
ELSE
-- Search for the request from the second table
SELECT COUNT(REQ_ID) INTO REQUESTFOUND FROM SECONDTABLE
WHERE SECONDTABLE.REQ_ID = REQUESTID;

IF(REQUESTFOUND > 0) THEN
-- Select the request details from second table
OPEN REQUEST FOR
SELECT REQ_TYPE, '-' AS REQ_TYPE_STATUS FROM SECONDTABLE
WHERE SECONDTABLE.REQ_ID = REQUESTID;
ELSE
-- Get the request from third table (will return as blank if nothing found)
OPEN REQUEST FOR

I am not sure there is a way to reduce the number of if-statements and nesting. What is possible though is to improve the performance of the conditions you have that check those conditions.

The count(*) is inefficient. Consider adding a 'ROWNUM = 1' condition to your where clause in a way that makes the following conditions possible:

SELECT COUNT(REQ_ID) INTO requestFound
        FROM FIRSTTABLE
        WHERE FIRSTTABLE.REQ_ID = REQUESTID
          AND (REQ_STATUS = 'D' OR REQ_STATUS = 'A')
          AND ROWNUM = 1;

This will be optimized by Oracle to exit the condition on the first matching result, ending up with a requestFound value that will be either 0, or 1. A value of 1 means there is some data, and does not indicate how much data.

Reading up on ROWNUM is useful. Ask Tom!

StackExchange Code Review Q#44963, answer score: 4