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 &gt; 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 &gt; 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
                              SELECT REQ_TYPE, '-' AS REQ_TYPE_STATUS  FROM THIRDTABLE
                              WHERE THIRDTABLE.REQ_ID = REQUESTID;
                      END IF;
            END IF;
END GET_REQUEST


My issues with this are as follows: 


It's not very concise, with a lot of nesting.
It's not very efficient, since it needs to perform a count, then if it exists it selects the details. That's two operations. How can I modify it to have a single operation for each search?


Also, the REQ_TYPE_STATUS column doesn't exist in the second and third table, so as you can see I have just assigned the value '-' to the returned result, indicating a blank value. I'm not really sure if there's a better or more standard way of doing this.
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!