Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"

I faced this problem while developing a Trigger in Oracle: ORA-01403: no data found. I did some research and understood the root of the problem. Nevertheless handling the error exception prevents the above error, but does not solve my problem.

What I am currently looking for is an optimal workaround to perform the lesser query amount/achieve the best performance as possible. I'll try to describe the scenario creating simple examples to the real structure.

Scenario

I have a "date reference" table to establish periods of time, 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 is triggered, I'll have one DATE field - say DATE_GIVEN (for example sake). What I need is:

1. To find the DATE_REFERENCE row in which DATE_GIVEN BETWEEN DATE_START AND DATE_END (easy); OR
2. If the previous option returns no data, I need to find the next closest DATE_START to DATE_GIVEN.

In both cases, I need to retrieve the row with all columns from table DATE_REFERENCE, no matter if it matches Opt 1 or 2. That's exactly where I faced the problem described.

I wrote this test block to test and try to find a solution. The example below is not working, I know; but it is exactly what I want to accomplish (in concept). I have added comments like -- Lots of code to make clear that will be part of a more elaborate 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;

Question

Knowing that the above PL/SQL block is more of a concept than working code, what is the best way to get RESULTROW populated, minding performance and the lesser queries as possible?

Sorry for the long question, but I figured scenario explanation was necessary. Thanks in advance for any help/thoughts!

3 Answers

Just populate the field directly, using ordering and 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;

This will populate the information with one query.

EDIT:

If you want to put a condition in the subquery, it needs to be:

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;

I believe the right condition is DATE_GIVEN <= DATE_END. This covers both the between condition and should imply DATE_GIVEN < DATE_START. This assumes that DATE_END is never NULL.

I also had similar problem, resolved it like this:

if the row does not exists in table LADDER.INCR_PROCESS I get IsPassed as Null:

Declare IsPassed Integer ;
Begin Select I.LVL Into IsPassed From LADDER.INCR_PROCESS I Right Join Dual on I.LVL >= 90010 and I.Passed = 0 Where RowNum = 1 ; ....
End;

I just have found new solution:

 Declare L_ID Integer; Begin select ( Select PREFERREDID from ladder.ric_exceptions where PREFERREDID = 1 ) into L_ID from dual; end;