Oracle PL/SQL - ORA-01403 "No data found" when using "SELECT INTO"
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
.