Oracle PL / SQL - ORA-01403 "Data not found" when using "SELECT INTO"

Question

I ran into this problem while developing a trigger in Oracle: ORA-01403: no data found . I did some research and realized the root of the problem. However, error exception handling prevents the above error, but does not solve my problem.

I am currently looking for the best solution to fulfill fewer requests / maximize performance. I will try to describe a scenario that creates simple examples for a real structure.

Scenario

I have a "date reference" table to set time periods, say:

CREATE TABLE DATE_REFERENCE (
    DATE_START                  DATE NOT NULL,
    DATE_END                    DATE NOT NULL,
    -- Several other columns here, this is just a silly example
    CONSTRAINT PK_DATE_REFERENCE PRIMARY KEY(DATE_START, DATE_END)
);

When the trigger fires, I will have one field DATE- let's say DATE_GIVEN(for example, sake). What I need:

• Find a line DATE_REFERENCEin which DATE_GIVEN BETWEEN DATE_START AND DATE_END(easy); OR
• If the previous option does not return data, I need to find the next closest DATE_STARTto DATE_GIVEN.

In both cases, I need to get a row with all the columns from the table DATE_REFERENCE, regardless of whether it matches options 1 or 2. It was there that I encountered the described problem.

I wrote this test block for testing and tried to find a solution. The example below does not work , I know; but that’s exactly what I want to accomplish (in concept). I added comments as -- Lots of codeto clarify what would be part of a more complex trigger:

DECLARE
    DATE_GIVEN       DATE; 
    RESULTROW        DATE_REFERENCE%ROWTYPE;
BEGIN

    -- Lots of code
    -- Lots of code
    -- Lots of code

    DATE_GIVEN := TO_DATE('2014-02-26 12:30:00', 'YYYY-MM-DD HH24:MI:SS');

    -- This one throws the ORA-01403 exception if no data was found
    SELECT 
       * INTO RESULTROW
    FROM
       DATE_REFERENCE
    WHERE
       DATE_GIVEN BETWEEN DATE_START AND DATE_END;

    -- If the above didn't throw exceptions, I would continue like so:
    IF RESULTROW IS NULL THEN

        SELECT 
           * INTO RESULTROW
        FROM
           DATE_REFERENCE
        WHERE
           DATE_START > DATE_GIVEN
           AND ROWNUM = 1
        ORDER BY DATE_START ASC;

    END IF;

    -- Now RESULTROW is populated, and the rest of the trigger code gets executed ~beautifully~

    -- Lots of code
    -- Lots of code
    -- Lots of code

END;

, PL/SQL , , RESULTROW , , ?

, , . /!

Answer 1

, rownum:

SELECT * INTO RESULTROW
FROM (SELECT *
      FROM DATE_REFERENCE
      ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
                     THEN 1 ELSE 0
                END) DESC,
               (DATE_START - DATE_GIVEN)
     ) t
WHERE rownum = 1;

.

EDIT:

, :

SELECT * INTO RESULTROW
FROM (SELECT *
      FROM DATE_REFERENCE
      WHERE DATE_GIVEN <= DATE_END
      ORDER BY (CASE WHEN DATE_GIVEN BETWEEN DATE_START AND DATE_END
                     THEN 1 ELSE 0
                END) DESC,
               (DATE_START - DATE_GIVEN)
     ) t
WHERE rownum = 1;

, DATE_GIVEN <= DATE_END. between, DATE_GIVEN < DATE_START. , DATE_END NULL.